performing calculations in forms and reports

[cdh]

I have the folowing fields- Start Time, End Time, Total Hours (calculates- =Format([Start Time]-1-[End Time], "Short Time")), Billing Rate, and Total Billed (calculates- =Sum([Toatal Hours]*[Billing Rate]). The Total Billed gives me an error. What expression should I use to get the calculated value of the Total Hours to be multiplied by the Billing Rate?

[pbaldy]

You can't sum a calculated control. Either move that calculation to the query underlying the report or sum the calculation:

=Sum(Format(...) * BillingRate)

You'll find in the long run that the spaces in your names are more trouble than they're worth.

[cdh]

Ok, so if I understand everything correctly I cannot perform a calculation on a calculated field? So what is the easiest way for me to have the form calculate total hours worked and total amount billed ( hours worked * billiing rate)? The sum expression was how I was trying to do it, but I could have been way off.

[pbaldy]

Did you try either of the two methods I mentioned?

[cdh]

Yes, however I am rather new and self taught from a book. So I may not understand what you have suggested completely. This is what I tried first. From the form- total billed (control source- =Sum(Format([Start Time]-1-[End Time,"Short Time")*[Billing Rate]). This still returns #Error. I then tried to configure a querry however I could never get it to work either. Thanks for your help!

[pbaldy]

If that's exactly what you tried, there's a bracket missing (only needed because of those spaces). Try:

=Sum(Format([Start Time]-1-[End Time],"Short Time")*[Billing Rate])

Also, make sure you don't have any controls with the same names as the fields, as this can confuse Access. Unfortunately, this is exactly what the wizard does.

[cdh]

Ok, that bracket was in there I just forgot it in the post. I have attached the database for you to review and see what can be done to make it work. I am working on the Required Positions Form. Thanks for all your help.

[pbaldy]

It occurs to me that the Format function is out of place there. It will cause the result to be treated as text. Take that out, and it should work. You'll run into the next problem, which is due to how a date/time field is stored. See if the solution comes to you.

[cdh]

Again I want to thank you for all your help. And I see that you are trying to teach me how, rather than just give me the answer, which I appreciate!

So I went and removed the Format from the =Format([Start Time]-1-[End Time],"Short Time") from the control source of the Total Hours colum. It now reads =([Start Time]-1-[End Time],"Short Time"). It does nothing, just returned a blank value in the Total Hours colum. I have played with it and still cannot get it to work, so not sure what I am doing different than you?

And without that working I cannot try to work through the next problem completely. However, this is what I know (but don't completely understand) about time and how it is stored in Access. Time is treated as a fraction of 24 hours, i.e. 9:00pm = .875 or 21 hours. Now I assume that what you were expecting to happen would be that the Total Hours colum would return the Access version of time and that I would have to convert it into Normal time using CDate() funtion. Now while I say all this, I do not completely understand how to get there.

I am a master of Excel and writing mathematical equations that work within it, I had no idea that Access equations would be so different. So it's hard to get my mind wrapped around it. For me the equation would look like this, Time 1 = T1, Time 2 =T2, Total Hours = TH, Billing Rate = BR, and Total Billing =TB, so (T2-T1)*BR=TB (which still doesn't compensate for crossing over midnight I know).

So with that it would seem to reason that in Access I can get the total time by =Format([Start Time]-1-[End Time],"Short Time"). And while I cannot perform a calculation on a calculated field, I should be able to get the total billed by =(Format([Start Time]-1-[End Time],"Short Time"))*[Billing Rate] and get the correct answer. However, the Format function returns the value as text. Add I am trying to remember, but I don't think text doesn't work for performing calculations.

So I think I kinda see what you are getting at and yet I am not sure how to get to the end of the problem. So how am I doing so far? And can you please help me stay on track?

Thank You

[pbaldy]

Well, this:

([Start Time]-1-[End Time],"Short Time")

still has the formatting portion of the Format() function in it, so you'll get an error. Try this within your formula:

([Start Time]-1-[End Time])

As you've discovered, date/time values are stored as a Double, where the integer portion is the number of days since whenever, and the fraction portion is the time. Given your example of .875 representing 21 hours, all you have to do is multiple by 24 to convert the decimal to number of hours.

By the way, I've also used the method RG gave you to calculate hours, but since you have the date as well you could also add the start date & time together and the end date and time together, then simply subtract to get the decimal difference, or use the DateDiff function.

[cdh]

I don't know what I am doing different than you. I input ([Start Time]-1-[End Time]) and it gives me a negative number, here is what the values are Start Time 8:00, End Time 23:00 and it returns -1.625. I have also tried ([End Time]-1-[Start Time]) and it returns -0.375.

[pbaldy]

This appears to work, either on its own or within a Sum():

=(([end date]+[End Time])-([start date]+[Start Time]))*24*[billing rate]

[cdh]

It works, but it doesn't work for times that cross midnight it returns a negative value. Have it calculat 5/21/10, 0800 - 5/21/10, 2300 billing rate $14 = $210, if you enter 5/21/10, 2300 - 5/22/10, 0800 billing rate $14 = ($210) or -$210. How can we madifiy this to work across midnight correctly? Thank you

[pbaldy]

I tested what I just posted with your dates and it correctly returned 126 (23:00 to 8:00 is 9 hours, times 14 is 126). I did have an issue with that other function returning a negative value. This one, because it combines the dates and times, should be reliable.

[cdh]

Sorry it took so long, but once I corrected the dates I was entering it worked great!!!!! Thanks for the help.