Help with UPDATE query

[Shaun]

Hi,

I have two columns in my Bookings table of type DATETIME -
Booking_Start_Date and Boking_End_Date. How can i update every row so that
all of the times for Booking_Start_Date are 09.00 and all of the times for
Booking_End_Date are 17.30, without affecting any of the dates?

Thanks for your help

 

[Allen Browne]

Use an Update query (Update on Query menu in query design).

In the Update row under the Booking_Start_Date field, enter:
DateAdd("h", 9, DateValue([Booking_Start_Date]))

In the Update row under the Booking_End_Date field, enter:
DateAdd("n", 1050, DateValue(Booking_End_Date))

Run the query.

Explanation:
DateValue() returns the date without any time component.
DateAdd() adds the desired number of hours or minutes.