Access 2003 checkbox to perform calculations

5 pts.
Tags:
Access 2003
CheckBox
Microsoft Access
I have modified the ledger template database from MS to incorporate 4 columns containing yes/no checkboxes. The checkboxes refer to 4 phases of contruction for the line entry. Some line entries may have nore than one box checked. My problem is that when I need to run a report on an individual phase, the line item should be divided by two if their are two boxes checked, or three if there are three boxes checked, etc. How do I accomplish this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I played around a little bit with that template and found something that seems to me to do what you want, but is not real user friendly. First I added a second yes/no field to the Transactions table that I called taxable1 and filled in a few entries. Then I opened the Transactions Listing report in design mode. Way up in the left corner is a square with a black square inside it. Right clicking that opens a list that allows you to get to the properties of the report. If you click on the line for Record Source, then click on the … on the right hand end, you get to see the SQL query that the report is built on. I clicked in an empty column and added an entry of IIF(taxable,1,0). Access names this as Expr1, or you can change it if you want. This Expr1 now contains the number one if taxable is yes or 0 if it is no. Do the same thing in the next column for taxable1. In the third column put in Expr1+Expr2 to total up the number of yes/no fields that are yes. Then I built one more column of WithdrawalAmount/Expr3 to give the original amount divided by the total number of yeses(Expr4). Then I closed all of that and went back to the report design. Instead of allowing the report to show WithdrawalAmount, I changed it to show Expr4. You would need to do something to make sure that there is no record that has no check boxes marked or that Expr4 is going to be WithdrawelAmount divided by zero.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following