How to calculate Total with a function

[irsmalik]

Hi friends

I have StartDate and EndDate at my form. I have found a Function GetElapsedTime(interval) which calculate hours & minutes when I give StartDate and Enddate.

At my form TimeSpent is a field and its Control Source is Function GetElapsedTime(interval)
=GetElapsedTimenew([Date2]-[Date])

Now I have to calculate TimeSpent with HourRate. I can't do it bcoz Function.

Can anybody help me.

Thanks
Irshad Ahmed

[Seth Schrock]

You could have another control with its control source being

=[TimeSpent] * [HourRate]

Personally I would do all the calculations in a query, including your TimeSpent field.

[irsmalik]

Dear Seth

It is not working. When I try to calculate it gives message.
"#Error".
I also try to run it through query but no result only gives this message.
When I enter Date1 and Date2....TimeSpent calculation is OK.
but it only shows at Form as running sum and does not save in the table. so TimeSpent field in table remain empty.
I assume that TimeSpent control source is a function, not a field, so it is not calculating.

Any more idea or guidance/suggestion. Thanks

[Seth Schrock]

You shouldn't store calculated values in tables anyway. Let me see if I've got your project correct. You have Date1, Date2, and HourRate which are entered by the user and then you want TimeSpent and Pay to be calculated? If this is correct, then do it in a query and then bind your form to the query.

SELECT Date1
, Date2
, HourRate
, GetElapsedTimenew(Nz([Date2], 0) - Nz([Date1], 0)) As TimeSpent
, Nz(TimeSpent, 0) * Nz(HourRate, 0) As Pay
FROM Table_Name

You will need to add any other fields that you need and put in your table name and you should be good to go.

[irsmalik]

Dear Seth

I tried your lines but result is same #Error

My project is attached. I will be really thankful if you please look at that where I am making mistake.

[Seth Schrock]

Please just post the SQL code as I don't open attachments.

[irsmalik]

OK... here is my query which I open in design mode but for you it is in SQL mode

SELECT [User Time].DATE, [User Time].DATE2, [User Time].RATE, GetElapsedTimenew(Nz([Date2],0)-Nz([Date],0)) AS TimeSpent, [Rate]*[TimeSpent] AS Amount
FROM [User Time];

I will appreciate if you plz open my attached query, it is bcoz i normally make query in query mode not in SQL.

Thanks

[Seth Schrock]

How you open the query doesn't change anything and the design mode is just Access putting an interface on the SQL code, which is what actually runs.

I notice a few different possible issues. First, Date is a reserved word in Access, and while it is allowed to be used as a field name, it can sometimes cause issues. I would recommend changing to something like Date1 or anything else that isn't a reserved word. You can Google what those are. Second, you don't have the Nz() function around either part of the calculation for Amount. Try adding those and see what happens.