Recombining Date and Time in a query 1

[TomCarnahan]

I am working with tables from another application where the Date and Time data elements have been separated. Instead of the Access data type that includes both (e.g. 5/23/07 8:07 AM), the date is separated (e.g. 5/23/07) from the time (e.g. 8:07 AM) into two fields.

The existing table records events with a [DateField], a [StartTimeField] and a [EndTimeField].

The algorithm I want to use requires that they be recombined in a single field. I figured out how to do it in most cases:

[DateField] + DateValue[TimeField]

This works fine except where the [EndTimeField] passes midnight. Example:

Date: 7/10/07
Start: 6:00 pm ==> "7/10/07 6:00 PM"
End: 11:00 pm ==> "7/10/07 11:00 pm"

BUT:

Date: 7/10/07
Start: 6:00 pm ==> "7/10/07 6:00 PM"
End: 12:00 am ==> "7/10/07"

Does anyone have an idea of how to work around this?

Thanks,










--- Tom

 

[maxhugen]

Interesting problem. As people we can describe an 'evening shift' as being 6 (pm) till 12... perfectly logical.

Can end times actually pass midnight, eg 12:30a, or would they always end at 12 midnight?

Max Hugen
Australia

 

[PHV]

You may try this:
[DateField]+DateValue([EndTimeField])-(DateValue([EndTimeField])<DateValue([StartTimeField]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[maxhugen]

Another question: will you need to use the result as a Date type, or can you 'present' it as a String?

If it really needs to be an actual Date type, you might have to subtract 1 minute from a midnight time, as you can't represent a Date of "7/10/07 12:00 midnight".

However, you could present that as a string...

Max Hugen
Australia

 

[TomCarnahan]

I am using the Start (Date and Time) and the End (Date and Time) as DATES because I want to use date math to do calculations. A string would not work as well and involve a lot more overhead.

--- Tom

 

[maxhugen]

Ok, for Date math you'll need PHV's solution, which adds one day, so that "7/10/07 12:00 midnight" correctly becomes "7/11/07 0:00 AM".

Max Hugen
Australia

 

[danielcan]

Good day sir, I had the same problem last week.

The formula I have used is; in your control window in the form design mode where you want the combining date and time to appear write this function =[date]&" "&[time]. Naturally date would equal the name you gave to that control and time the same thing.

It works simply and effeciently.

Daniel Belanger