Union Query issue...

[elsanto]

Hey all,

I have a problem with a Union Query. The problem is thus:

I have approximately a dozen Excel spreadsheets updated daily by different staff members.
These spreadsheets are linked to a single Access database.

I have a union query (CombineAll) that, as the name suggests, combines all the information from the spreadsheets.

The results of the query display the Sent field as a text field, rather than date/time. The format should be "dd/mm/yy hh:nn:ss".

I need to match the Sent field against a Received field, and get a time difference. Of course this isn't going work if I'm matching a date/time field (Received) against a text field (Sent).

Due to the nature of a union query, once the sql mode is activated, I can't switch back to design mode and adjust the properties of the Sent field. Or can I?

I'm stumped. Any ideas?

Cheers.

 

[Mile-O]

Use the CDate() function in your SQL to convert the text field to a date/time field.

Changing any reference to Sent to CDate(Sent) AS SentTime.