number to date format 1

[jkupov]

I receive a monthly .txt file that I import into Access. The dates are written in such a way that 061505 is 06/15/05. When I import the date file into Access I have to designate the field as a number rather than date. If I change the field to a date/time field the date appears as 5/22/2068. So... I have been leaving the fields as numbers and dealing with it. Is there a function that will allow me, while querying this table, to convert it to date/time without altering it to 05/22/2068? I want to query it against a date in a different DB that has dates stored as date/time. The reason why I query it against the other DB is to find the matching record in our database that corresponds with the record in the txt file.

Any ideas. Thanks.

 

[PHV]

Import the field as text (to preserve the keading 0) and then you may use this expression in a query:
DateSerial(Right([yourField],2), Left([yourField],2), Mid([yourField],3,2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[psemianonymous]

The Access text import wizard has an option under "advanced" to determine date delimiter and date format. Erase the date delimiter, set your date format to MMDDYY, and it will magically import this to a date as you intended. And yes, this is in fact brutally easy.

 

[jkupov]

Thank you both for the suggestions. Both worked wonderfully!