Change date format

  • Thread starter Thread starter Paul Tri via AccessMonster.com
  • Start date Start date
P

Paul Tri via AccessMonster.com

I am trying to create a query that will join 2 tables together when they
have the same first and last name and same Date Of Birth....however the one
table is from a different database and it is in the following format:

If the date of birth was 12011972 the database shows it as 1721201 or if
the birthdate was 01311994 it shows 1940131

Basically YYYMMDD

Is it somehow possible to query these and have it decipher between the 2
formats?

Thank you!!

Paul
 
Tables all store dates the exact same way (if the field is truly a date
field). The format in which the date is displayed does not matter when
comparing fields.

If the fields are NOT date fields, then you will have to get the 'date
value' of the number and compare that. Personally, I would make all my date
fields into DATES.
 
Thanks RICK B, the data is not stored as a true date. The program that
reads from this database uses this crazy format for reasons I am not aware.
I need to be able to change it though....I recieved the following as advice
but am not sure how to use it in a query.....

yyymmdd2date: DateSerial(1800+Left(yyymmdd,3),Mid(yyymmdd,4,2),Right
(yyymmdd,2))

mmddyyyy2date: DateSerial(Right(mmddyyyy,4),Left(mmddyyyy,2),Mid
(mmddyyyy,3,2))

Thanks,

Paul
 
Thanks RICK B, the data is not stored as a true date. The program that
reads from this database uses this crazy format for reasons I am not aware.
I need to be able to change it though....I recieved the following as advice
but am not sure how to use it in a query.....

yyymmdd2date: DateSerial(1800+Left(yyymmdd,3),Mid(yyymmdd,4,2),Right
(yyymmdd,2))

mmddyyyy2date: DateSerial(Right(mmddyyyy,4),Left(mmddyyyy,2),Mid
(mmddyyyy,3,2))
Click to expand...

If you put these two expressions in two vacant Field cells in the
query grid, you'll get dynamically calculated dates from the text
fields.

You might do better to add two new Date/Time fields to your table, and
use an Update query. Put the DateSerial() function calls in the Update
To line of the query and run it to permanently store the real dates.

John W. Vinson[MVP]
 
Back
Top