By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

How to calculate Total with a function

P: 85
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
Jan 27 '15 #1
Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
You could have another control with its control source being
Expand|Select|Wrap|Line Numbers
  1. =[TimeSpent] * [HourRate]
Personally I would do all the calculations in a query, including your TimeSpent field.
Jan 27 '15 #2

P: 85
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
Jan 28 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Date1
  2. , Date2
  3. , HourRate
  4. , GetElapsedTimenew(Nz([Date2], 0) - Nz([Date1], 0)) As TimeSpent
  5. , Nz(TimeSpent, 0) * Nz(HourRate, 0) As Pay
  6. 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.
Jan 28 '15 #4

P: 85
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.
Attached Files
File Type: zip RATES.zip (56.0 KB, 29 views)
Jan 28 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,951
Please just post the SQL code as I don't open attachments.
Jan 28 '15 #6

P: 85
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
Jan 28 '15 #7

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jan 28 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.