Calculation for time x hours in table

[hellojosie]

Hi,

I greatly appreciate any help in advance!

I need to calculate in the tbleJobs table, the amount of the jobs total amount and place the result in the JobsTotalAmount field.

This has to be a manual calculation. Not an on event calculation.

Data is coming from two table:

tblLimos has the hourly rate.

tblJobs has the start time, the finish time and the total amount field.

Thanks for looking!

[June7]

Why are you trying to save calculated results? A principal of relational database is enter raw data, do calcs in reports. Tables hold data, queries manipulate it. Do calcs in queries or in textboxes on form or report.

[hellojosie]

Thank you June7 for your reply.

I agree with you.

I am a student studying access and it is a requirement for an assignment that I set up this calc in a table.

Is it possible?

I did set up a query that shows:

JobsTotalAmount: DateDiff("n" [JobStartTime],[JobReturnTime])*[LimoHourlyRate]

This formula is entered in the fieild, field.

But this just returns a pop up box for a parameter query that says "JobStartTime".

Thanks again for replying I have been working on this for hours and hours of the last few days.

[June7]

Access2007 does not allow calculations directly in table (new feature in Access2010). Must do in query or textbox on form. Then VBA code behind form can save the calculated result to table field.

I assume the comma missing in front of JobStartTime is a post typo because Access would error on that syntax flaw.

The prompt is because Access can't find a field called JobStartTime. Show the complete query SQL statement for analysis.

The parameter for hours is "hh", not "n" which is for minutes.

[hellojosie]

This is the exact formula now and I don't know why but it is displaying #func! in the JobsTotalAmount field.

JobsTotalAmount: DateDiff("hh",[JobStartTime],[JobReturnTime])*[LimoHourlyRate]

Data fields in the tables are

JobStartTime & JobReturnTime- Date/Time, Short Time
LimoHourlyRate-Number
JobsTotalAmount-Number

Thankyou again!

[hellojosie]

I ended up entering this formula into a query and it works!!
JobsTotalAmount: DateDiff("h",[JobStartTime],[JobReturnTime])*[LimoHourlyRate]
Thanks again for your help June7

[June7]

Microsoft article said the parameter for hours was 'hh' but I guess it is really 'h'. Not something I have used much. Glad you got it working.