Need the ability to override a total taken from a table

[June7]

Try it and see what happens.

I've never had to deal with times, only dates, in my dbs.

It isn't a valid time without AM/PM.



Can probably input data as 24 hr clock time and Access can deal with the format conversion to AM/PM.

Be aware that midnight is saved without a time part (the decimal is 0), which does complicate sorting of records and calculations if value saved is exactly midnight.

[bradp1979]

It worked to an extent. It's giving me a positive number, but it's reflected as a decimal. I can't get it to read as a time. When I try to change the format of the textbox, it's not giving me time as an option.

[June7]

That's because the textbox is UNBOUND with an expression as ControlSource and not BOUND to date/time field. You are basically damned if you do and damned if you don't. I am glad I don't have to deal with elapsed date/time calcs.

[bradp1979]

It's bound to the WkHrs field on qryRelEventEmployee... In the textbox it just says =[WkHrs]. I don't know. I'm about to just say "Hell with it" and tell everyone in the office that they just ignore the time calculation if the event went past midnight.

[bradp1979]

Is there a way to have the expression remain DateDiff("n",[StartTime],[EndTime])/60, while having the IIf statement either added to the end, or added as criteria? Or is there another IIf statement that I could add as criteria, something that essentially says IIf([WkHrs]<1, then [WkHrs]+24)?

[June7]

Don't use = sign for binding directly to field. An = sign means an expression, not bound to field.

Can have code in an event to do whatever you want with the user input. Then set the value to the calculated result. Use the textbox AfterUpdate event.

[bradp1979]

Ok, I've tried a couple options in the Expression Builder for AfterUpdate event, but they're not yielding positive results. I put IIf([EndTime]>[StartTime],[EndTime]-[StartTime],([EndTime]+1)-[StartTime]), and I just kept getting error messages. I also tried IIf([WkHrs]<0, +24), but when I clicked off of the line, it changed to IIf WkHrs<0, 24 - which didn't do anything on the form.

Do I use Code Builder, Expression Builder, or Macro builder, and what do I input to get it to do anything?

[June7]

I never use the Expression Builder (basically avoid most of the Wizards). I don't use macros, only VBA. If you want to allow user to input into bound textbox and then code take that input and do something with it, do VBA in the textbox AfterUpdate event.

A simple example from my db:

Private Sub tbxFieldLL_AfterUpdate()
Me.tbxFieldLL = UCase(Me.tbxFieldLL)
End Sub

[bradp1979]

Incidentally, I got it working. I put WkHrs: IIf([EndTime]>[StartTime],[EndTime]-[StartTime],([EndTime]+1)-[StartTime]) as the control source, and formatted the text box as hh:mm, and it works like a charm.

[bradp1979]

Ok, "Like a charm" is a bit of an overstatement. Although it shows times past midnight nicely as positive times, when it's all being calculated in the footer as "Total Emp Hours," if the total time is 24 hours, it's showing as 0:00. If it's 26 hours, it's showing as 2:00. I've tried a bunch of things, but I can't get it to work. I've tried to work in something in the expression builder, utilizing built-in functions, but I'm not seeing them do anything at all.

[ssanfu]

and it works like a charm.

Really?? I don't think so.... You CANNOT subtract a time from another time and get a time!!
Subtracting a time from another time results in a duration!

Using your formula and formatting, if the start time is 6:00PM and the end time is 10:30pm, your answer is 4:30. That is NOT 4 hours and 30 minutes. It is 4:30AM.
If you remove the formatting, you will see that the result is actually 0.1875.
If you then multiply that by 24 (hours in a day), the result is 4.5 HOURS. (the correct answer, BTW)
( remember that the zero is the number of days and the .1875 is the portion of a day. That is why you multiply by 24.)

How can you multiply the hourly rate by a time?? I get $7.00 per hour multiplied by 4:30 (am)????

The point is: Do not format using Short Time.


See
Store Date/Time data
https://support.microsoft.com/en-us/kb/210276

[bradp1979]

Awesome. Since in the Total Emp Hours it was showing a decimal, I have it now as =(Sum([WkHrs]))*24, and it's got the right number.

Thank you!!