Change time portion of datetime field in update query?

[muchjubble]

Hi guys

I have some incorrect time entries in a column that I need to fix with an update query.

So, 04/11/2013 08:00:00 needs to be changed to 04/11/2013 09:00:00

Can you help me out?

Many thanks

 

[pr2-eugin]

Try

DateAdd("h", 1, yourDateFieldName)

 

[muchjubble]

Aaah Bournemouth, my home town

Thanks pr2-eugin.

It's not always an hour wrong unfortunately.

So I guess the entries that are correct already can be filtered out by adding criteria, but how can I access the time portion in order to do so?

Show all entries that don't equal 09:00:00 and update them to 09:00:00

Many thanks

 

[pr2-eugin]

It's not always an hour wrong unfortunately.

Okay that part was not mentioned in the first post..

Well if you want to take the Time part then use the TimeValue Function..

UPDATE yourTableName SET yourTableName.dateTimeField = #09:00:00#
WHERE ((TimeValue(yourTableName.dateTimeField)<> #09:00:00#));

Think that should do the trick.. Oh and yes change the field and table names to match up your design.

Aaah Bournemouth, my home town

Why did you move out then? Feels like I am the only person in Bournemouth doing Access. LOL. You can call me Paul.

 

[muchjubble]

oops! yep forgot to mention that, sorry

Thanks Paul, that did the trick

Well I moved to San Francisco for a while, but then ended up settling down in Sherborne! I still get down to Bmth regularly though, such a lovely place.

Thanks for your help and nice to meet you, cheers!

 

[pr2-eugin]

ended up settling down in Sherborne! I still get down to Bmth regularly though, such a lovely place.

It sure is a lovely place. Sherborne not really far as San Fransisco though..

Thanks for your help and nice to meet you, cheers!

Glad to be of help, nice to know you too. Good Luck and Good day.