Calculating two fields in same form and displaying result

[rhubarb]

I have a module that calculates time. I access this module using a query called totalhours which calculates the time from the start of a job to the end of a job. I have a table which has fields for Job start, Job end, and Total hours. How can I get my query to display in the total hours field on the table?



I'd like it to calculate the totalhours as soon as someone finishes entering information into the job end field.

If I use a lookup it complains I am selecting a query that uses the table I'm adding the lookup column to. If I attempt to use a calculated field and input my expression it says my expression cannot be used in a calculated column.

Any ideas? Let me know if I haven't provided enough info.

Thank you!

[rpeare]

You really do not want to store calculated values in your table. You can perform any sort of calculation you want in a query without having to store that extra piece of data. You can get the hour difference by using the formula

TotalHours: datediff("h", starttime, endtime)

I'm not sure why you'd need a function to do this unless the calculation is more complex than you've mentioned.

[rhubarb]

The datediff formula doesn't seem to work either. I don't think it likes using two of the fields in the table to derive its answer. I am using datediff("h",[job start],[job end]), where job start and job end are two date fields in current table, which results in error.

It looks like I just may stick to a report or the query for total hours. Seems to be best way.

Thanks

[rpeare]

Can you post a zipped up example of your database. It'll be easier to find the problem with an example of your data/query.

[rhubarb]

I have zipped and attached my DB. Sorry for the delay.

So what I am trying to do is have it so that on the Main form the Total Hours is empty until someone enters both Start time and Time left Bldg. Once that event occurs the calculation should take place and the total hours should be displayed and dropped in table. I am ok with rounding to the nearest hour as that may be easier. so maybe start time and time left could be rounded to nearest half after entered first.

I also just found I am having issues even with the calculation itself. You can see in my Total Hours query if I enter Start time of 11:00 PM and an end time of 2:00 AM I get a negative incorrect answer. The reverse does work though if I enter AM to PM. It's once I cross the 24 hour mark. Maybe there is a simpler way of doing this. The module CalcTime is not mine so I do not take credit for it.

Thanks for any help.

[rpeare]

Please note my version, I can't open 2010 files.

[rhubarb]

I saved in .mdb format but it only saves the tables. When I attempt to create a quick form it does not save the object. Should be able to get an idea of what I am doing though just from tables. If you create a form and pull all fields from table. You will get the start time and end time. My two issues from above are the same: calculating PM -> AM results in error, second getting it to display on the form. Let me know if you can see the module. It may not have saved either.

I don't think it attached the module so I'll add it as separate text file. It is not my code so I do not take credit. Also I attached query logic.

Thanks for any assistance.

[rpeare]

Create a query based on your TICKETS table (by the way you really should not use spaces in field or object names in your database, it will just cause you grief as you go along).

In that query put in this calculated field:

Expr1: IIf([time left bldg]<[start time],(1440-DateDiff("n",[time left bldg],[start time]))/60,DateDiff("n",[start time],[time left bldg])/60)

This calculation will correctly calculate a decimal number of hours from the start time to the time left bldg fields assuming that you can never have more than a 24 hour period between the start and end time. Once you have this in a query you can do whatever you want with it in reports etc.

[rhubarb]

Thank you very much for the response I will try that out and follow-up.

[rhubarb]

That did the trick! Thanks so much!

Not sure how to mark this as solved.