Green Boxes in Access 2007 form after deleting O’s & 1’s from database

20 pts.
Tags:
Access 2007 forms
Access 2007 functions
Access Database
Microsoft Access
Microsoft Access 2007
I have a database tracking the information turned in on a hard copy form. If a patient triggers specific questions the data must be input: so if someone triggers the depression, I in their suicide rating, treatment plan, etc... I have the access form use check boxes but the data base use -1's. I need the database to calculate/count how many of each questions and answers are triggered, so there cannot be any 0's... When I go in and delete the 0's however it leaves green boxes in the form. How do I get rid of these green boxes? How do I get the form to just enter -1 for a check, and nothing if it's left blank?

Software/Hardware used:
Microsoft Access 2007

Answer Wiki

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

The green boxes are shading indicating that the value is null currently because you cleared out the value. A check box object is either yes or no / true or false / -1 or 0. If you want a count checked values you need to count where the value is true only.

Ex. Say you have yes/no columns in your table called box1 and box2. If you want a count of how many of either are true, you could do the following query

Select count(*) from table where box1 or box2
or you could word it this way:
Select count(*) from table where box1 = true or box2 = true
or you could word it this way:
Select count(*) from table where box1 = -1 or box 2 = -1

Discuss This Question: 2  Replies

 
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
  • ARickert
    I can obtain the count just by using the total function, I have it count the number of -1's to give me the positive value of the sum, however to do this I need there to not be 0's. I have to create a new database and form for each month, so I don't want to many query's and reports having to be pulled to accomplish this. "How do I get the form to just enter -1 for a check, and nothing if it's left blank?"
    20 pointsBadges:
    report
  • Meandyou
    Use the suggestion you already have. I don't intend to sound severe, but you really need to learn how to use SQL And create a new database each month? I don't intend to sound sever, but you really need to learn how to use an RDBMS
    5,220 pointsBadges:
    report

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