Linking Dates

[cg084]

Hi all,

I have got 2 tables in Access both require to be linked by date. In the dataTable the date is in the format dd/mm/yy hh:mm:ss format. The dateTable uses a date in the format of dd/mm/yy and nothing else is stored on this. The dataTable i have no control of it is an automated file sent to me.
The problem i am getting is being able to match the dates. Only some actually match. About 42 in 9000 or so records.

I know that this is because the date 10/11/03 00:05:00 is larger than 10/11/03 and are therefore not exaclty the same so will not correctly link my 2 tables.

Is there another way around this. I have tried to change the data formats of them and still no luck.

Any help recieved gratefully.

 

[KenReay]

Hi

If you do not actually need the hh:mm:ss portion of the date, why not:

Create another column or type date/Time format dd/mm/yy

Make an update query to pupulaet this with the date portion of the original date column

Use the new column in the join



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulBricker]

If you don't want to have to update the info all the time I would just take Ken's suggestion one step less. Create a query using your dataTable, create a field

MyLinkField:CDate(Format([DateField],"mm/dd/yy&quot)

and then join this query to your dateTable. Then your query is always current with records in your dataTable.

Paul

 

[dhookom]

If your resultset doesn't need to be editable, you can set the criteria under the date only field to the expression:
DateValue([OtherDateField])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.