Wierd Date format

[ptrifile]

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

 

[PHV]

A starting point:
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))

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

 

[ptrifile]

PHV, Thanks! However, I am not sure what the next step would be on making this work in a query...I took a couple stabs at it but could not make it work. If you could elaborate a little on this I would appreciate it beyond imagination!

Thanks!!
Paul

 

[lespaul]

what's the query you've almost got working?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[ptrifile]

Hi lespaul, I dont really have anything almost working. I didnt know where to begin using that code so I just started putting it anywhere I could in the query but to no avail.....any tips would be great! Thanks!

Paul