We are running ODBC to an AS400. I'm having a problem with the ISO Date Defaults. We use the standard '0001-01-01'. If I add such a date to an Access query through a Table linked to the AS400 source, it will bring in the date just fine. However, it displays it as 01/01/1901 and if I try to use it for selection or in a filter, I get zero records. I have tried using #01/01/01# and #01/01/1901#. I have forced the yyyy-mm-dd format and tried #0001-01-01#. I really need for Access to recognize it as null, but none of the null functions work either. If I display a table and do a "Filter by Selection" it will select only those with the 01/01/1901, but I need to automate the process to extract all records with this Null(to an AS400)date through a query. Any suggestions, what am I not understanding?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
AS400 Dates in MS Access Process
- Thread starter PaulaJ
- Start date
It sounds like your imported dates are not coming in as valid dates. Could you bring it in as text and have a query convert it to a date by parsing out the year, month, and day? If you are really only worried about obtaining a list where the date field is null, have you tried "DateField>0" as your criteria instead of "DateField Is Null"?
-Larry
-Larry
- Thread starter
- #3
Thank you. Importing it to a text field seems to have been the ticket. Access will now recognize the "Null AS400" date if I compare it to '1/1/1901'. Luckily I don't really need to use this imported date for any actual date functions.
- Status