Form Calculations (Access 2000 (9.0.3821 SR-1))

Topic

New Reply

Folks,

Have I told you all how wonderful you are to people like me who have just enough learnin’ to be dangerous? It was my bright idea to learn Access so we could keep up with all the information on our class action suits for farm workers. Now I can’t seem to make it do anything I want it to!

OK, enough moaning Here’s the problem:

I need to show a calculation done on a form in a query or report, or filter the form to show the information. But neither of those works for me. Basically, I want to sort for all records before 01/01/2003 that show a calculated total on the form of less than $6.50, and also for all records after 01/01/2003 that show a calculated total of less than $6.90. We’re a non-profit law firm trying to figure out if the employer is paying the minimum wage to his employees. I can’t get it to show me anything but zeros when I put the criteria in. I’ve tried running this as a query, and as just an advanced filter on the form.

Any help would be appreciated. I have to have this by monday for the attorneys.

Ever grateful – Tia

Reply | Quote

Replies

This shouldn’t be too hard. You mention “a calculated total on the form”. If the total is really calculated on the form itself, you should put it in a query. Create a query based on the record source of the form (a table or a query), and add the calculated total as a calculated field (if you don’t know how to do this, post the control source of the calculated total). The criteria for the query would look like the attached screenshot (with other field names, of course, and US number and date formats instead of Dutch). If you need more help, post back with more specific information.

Reply | Quote

Hans, I’m sorry. I don’t know what you mean when you say “post the control source of the calculated total.” I understand the criteria part, and had that done correctly. This is what I have for the control source in the form: ” =IIf([Pay_Stub_Hrs]=0,0,(([Gross_Pay]+[Bonus_Amt])-([Deduction1_Amount]+[Deduction2_Amount]))/[Pay_Stub_Hrs]).” Where do I put (post?) this to get it to calculate in the query?

Thank you!

Reply | Quote

What I meant by “post’ was just what you have done: include the control source in a post in the Lounge. So at the moment, you have a control on the form to calculate the total. We’re going to calculate it in the qury instead – see screen shot. Once you have done that, you can impose selection criteria on the calculated total. The control source of the control in the form can be replaced by CalculatedTotal. (Once again, formats in the screen shot are non-US, but the idea should be clear, I hope)

Reply | Quote

Hans:
Thank you so much! I got it, and the attorneys are happy. (Which, of course, is all that matters — I exist only to serve! )

Reply | Quote