Long Date Format Very Tricky

[mr moe]

Hi all,
I have a table that has a long date format for example 05/05/05 02:40:34 AM how can I add an 8 hours to the date and time. Please help if you can because I"m going nuts!!! thanks. I want it to look like 05/05/05 10:40:34 AM

 

[Pat Hartman]

Use the DateAdd() function.

 

[mr moe]

It's not working all I want to do is to convert GMT time to Standard time. I want this field to be converted. for example 04/04/04 03:35:22 AM will have to be converted to the date above minus five hours. can someone please help.

 

[Pat Hartman]

The DateAdd() function IS the method you need to use. It DOES work. Perhaps you have made a coding error. We'll never know unless you post your query.

 

[mr moe]

Pat lets assume that the field name in my query is mytime and the table name is Test, Can you please give an example of how I would use the DateAdd function. Like I said I want to subtract or lets say add 4 hours for the mytime field. thanks.

 

[Pat Hartman]

DateAdd("h", -4, mytime)

Does your help not work?

 

[mr moe]

Thanks Pat,
but I'm getting data type mismatch when I run my query, again the field is long date format. Thanks, please advise.

 

[Pat Hartman]

mytime must be defined as a date/time field. If you are formatting it before doing the calculation, you have turned it in to a text field and that is what is causing the problem. NEVER format date/time fields prior to using them. Format them in the form/report to avoid problems like this.

 

[mr moe]

Pat just to correct something the datatype is date/time and the format is general date, I hope that can help.

 

[mr moe]

Sorry Pat it's still not working, keeps telling me type mismatch.

 

[Pat Hartman]

All I can tell you is that if Access is complaining about a type mismatch, your date is NOT a date/time data type at the time it is used in the DateAdd().

The format of a date/time field is irrelevant. Date/time fields are stored as "serial numbers". They are formatted only for display purposes.

You don't seem to be willing to post your query so hope you figure this out. I'm off to bed.

 

[mr moe]

Ok Pat thanks have a good night, I'm ready to go to bed too. I'll keep you posted Sir,
Bye,