Time calculations in Access

[Jim0845]

Can I do time calculations in Access? I know I can in Excel, and I can do date calculations in Access but I can't find anything in Help about time calculations short of writing code. I haven't used Access in over 10 years and never did much with VB.

What I want to do specifically is update a checkbox when a time on a form is anything less than another time + 1 hour on the form.

A submission window is 12:00PM to 3:00PM. If a submission is made by 1:00PM or sooner a credit is earned.

UPDATE Personnel_Actions SET Personnel_Actions.Early_PR_Credit_Earned = Yes
WHERE (((Personnel_Actions.PA_ID)=[Forms]![PPI_Main_Form].[PA_ID]) AND (([Forms]![PPI_Main_Form].[Time_PR_Rcvd])<=([Forms]![PPI_Main_Form].[PR_Time_Beg_Slot]+1)));

This query updates the checkbox regardless of the value in the +1 position whether the submission time is 1 minute or 2 hours past the "credit earned" window.

I use a similar update query to set flags using this method with no problem (date_field + 14 for example).

Am I trying to do something that can't be done in Access without code?

 

[SkipVought]

Check out TimeValue.

It seems that your values also contain the DATE (integer) part of DateTime. TimeValue will return ONLY the fractional part.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Have a look at the DateAdd function too.

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

 

[Jim0845]

Guys, thanks for the quick response.

PHV, I can use DateAdd("h", 1, "12:00 PM")
as an expression in a text box and it returns 1:00 PM, so this is close to what I need. However, the time in the field in question will vary from record to record. When I try to use the function in a query in the form of:

<=(DateAdd("h", 1, "[Location].[PR_Time_Beg_Slot]")

with or without the quotes, I get a syntax error. Can it be used in this fashion? If so, any help on the proper syntax would be appreciated. I have tried every variation I can think of, and readily admit to frequently overlooking the obvious.

Skip, the actual values in the fields I am testing with are 12:00 PM (the field I want to add an hour to) and either 1:01 PM or 2:30 PM in the field I am comparing against the calculated time. I’m not sure about your reference to “your values also contain the DATE (integer) part of DateTime”. When I checked out TimeValue it looks like that is something I would use if I break down and try to figure out how to use VB code, which I am trying to avoid at this point.

Thanks again for your responses and any further suggestions will be greatly appreciated.

 

[Jim0845]

When I stopped trying to write the sql myself and typed

<=(DateAdd("h",1,[Forms]![PPI_Main_Form].[PR_Time_Beg_Slot]))

into the criteria box in the query wizard it worked. I'm still not sure where my sql was wrong, but I will figure that out later. Thanks again for your help.

 

[JBinQLD]

G'day Jim,

Notice the working version qualifies the control fully:

forms!FormName!ControlName

whereas your version omitted the "forms!":

location.controlname

Also, as is plain, the form name is different in the working version but i assume that's cos you renamed it or something!

Glad you got it working and good luck,

JB

 

[Jim0845]

Thanks JB. Actually, in the first version I was comparing against a value in a table. After I got it working I decided to do the comparison against a field on the form.

Thanks again to all of you for your help.