Query join Date\Time confusion

[Steve B]

Hi

I have a table that is produced from a download from a Index telephone
switch.

It stores the date and time data in one column in the format:

07/12/2007 08:42:23

The switch also produces an agent table tha shows their login, log off,
free, busy status. However this stores just the Time in

08:42:23 Format

Now I need to list the agent status against the long date time format.
However I join the two tables by the date columns but access doesnt match
due to the Date being on the left column.

I've tried various methods, I.e. creating a query that looks like this

Date: =date() TimeID =[Date] & " " & [TimeId]

To get the output to join the Date and time in one column, but it wasnt
having any of it.

Any idea's?

 

[Douglas J. Steele]

Dates and times are stored as 8 byte floating point numbers where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

That means you can simply add the date and time value together using +:

Date() + [TimeId]

 

[Steve B]

Thanks Doug, worked a treat.

Dates and times are stored as 8 byte floating point numbers where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

That means you can simply add the date and time value together using +:

Date() + [TimeId]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi

I have a table that is produced from a download from a Index telephone
switch.

It stores the date and time data in one column in the format:

07/12/2007 08:42:23

The switch also produces an agent table tha shows their login, log off,
free, busy status. However this stores just the Time in

08:42:23 Format

Now I need to list the agent status against the long date time format.
However I join the two tables by the date columns but access doesnt match
due to the Date being on the left column.

I've tried various methods, I.e. creating a query that looks like this

Date: =date() TimeID =[Date] & " " & [TimeId]

To get the output to join the Date and time in one column, but it wasnt
having any of it.

Any idea's?