converting text to date

[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.

 

[Guest]

DateSerial(Right([ONDATE01] ,4), Left([ONDATE01], 2),Mid([ONDATE01], 4, 2))

 

[karen scheu via AccessMonster.com]

Thanks for responding, but that gave me an error. I don't get why the cdate
function does not work?

Thanks.

 

[Guest]

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

 

[karen scheu via AccessMonster.com]

Thanks very much. My date format was actually mmddyy and the dateserial
function worked.