converting text to date

  • Thread starter Thread starter karen scheu via AccessMonster.com
  • Start date Start date
K

karen scheu via AccessMonster.com

I imported a file from AS400 and am trying to run an append query into an
access table. The access table contains a date/time field and the as400
stores that field in text format (mm/dd/yyyy). In my append query I do the
following on the [field line] in the query:
IIf([ONDATE01]=" ",Null,CDate([ONDATE01]))

The [append to] line contains the date/time field [GL]

I get an error message stating Access set 0 fields to null value due to type
conversion failure. If I create a test query using the AS400 table, the
above statement returns the data with no errors, but when I run the append
query, the append into a table with the date field does not work. I can't
figure this one out. Please help me. Thanks.
 
Thanks for responding, but that gave me an error. I don't get why the cdate
function does not work?

Thanks.
 
It has to do with the format of the data you are getting. You stated in your
original post that the format is "mm/dd/yyyy". I would take that to mean
that today's date would be - 06/24/2005 - If this is true, then the CDate
will work. It will not work if it is really 06242005. I suspect this to be
the case, because the function I sent does work if you change it a bit. I
used the string functions based on string length of 10, not 9

So, If your data really is 06/24/2005 then the CDate will work.
If it is 06242005 then dateserial(right(x,4),left(x,2),mid(x,3,2) where x is
your data
 
Thanks very much. My date format was actually mmddyy and the dateserial
function worked.
 
Back
Top