By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Help with UPDATE query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Add 2 more columns to your table: StartTime, EndTime, make them dateTime
fields. Then here is some code you can run

Sub UpdateTbl()
DoCmd.RunSql "Update yourTbl Set StartTime = #09:00#, " _
& "EndTime = #17:30#"
End Sub

Open up a standard module (Modules Tab) and paste this code in the
module (change yourTbl to the name of your table). While the cursor is
inbetween Sub UpdateTbl() and End Sub you can press F5 to run the code
(on works in Standard module - In a Form code module you can't use F5).
Note: you will get a message stating that ## Rows will be updated. Just
click yes to continue.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

P: n/a
"Shaun" <sh***@mania.plus.com> wrote in
news:kd******************@wards.force9.net:
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

SET Booking_start_Date =
Dateserial(
year(booking_start_date),
month(booking_start_date),
Day(booking_start_date))
+timeserial(9,0,0)

HTH,

Bob Q


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.