Running a Query from Checkboxes

[sha7jpm]

help!

I am trying to create a query based on the checkboxes a user selects within a form.

The idea being that the user chooses from a selection of fields via checkboxes.

the query then runs an If statement on the checkbox (if checkbox = Yes then show all blank records in that field)
and brings up the field(s) which the user required.

there are two main problems.
1. the query just brings up a popup box rather than looking itself to see whether the checkbox has been ticked,

2. Also the tickbox does not seem to save , so regardless of what you tick, if you close the form and re-enter all boxes are unticked.

any help, or pointers on how I should be doing this rather than the route I am taking would be great.

cheers

John

 

[AndyBannister]

John,

I hope I am clear in my mind what you are trying to do --- namely use a form as a kind of dialogue box driving the query. (e.g. the check box does not exist within the table you are querying). If this is correct, then the problem would be appear to be that your query is not referencing the form correctly. What you need to do is, rather than simply the name of the checkbox, in your query column put:

forms![name of form]![name of checkbox]

and in the criteria row put:

TRUE

That will cause the query to check whether the user has ticked the check box on the form (which must be open for the query to run).

If that wasn't what you were trying to do, can you provide a little more information.

Cheers,

Andy

 

[sha7jpm]

Thanks, now the next step!!

Andy

that works, many thanks, I had put the expression on the incorrect row within the query.

my next step is to bring up only those records where they are blank within that field (call it pupil name)

so, the user selects the project field checkbox.
the query picks this up, and runs the query based on all the blanks in the pupil name field

I seem to be able to reference the query column to the expression but the result is an empty table.

the expression I used within the criteria of the pupil name field is
"if «Expr» [Expr1]"="true" Is Null

therefore is the checkbox is ticked, bring up all records which are blank for that particular field.

ta

john

 

[AndyBannister]

I think the problem is that Access is testing the check box for a null value rather than the field in the table if you write the formula that way. Off the top of my head, try this:

1. Create a new column in your query

2. In the field put:

forms![form name]![check box]=true AND isnull([pupilname])
(replacing with your field/form names as appropriate)

In the criteria row for that column put:

True

I think that should do it. For each record in your table, Access will evaluate the expression above, which will only be true if a) the checkbox is ticked and the field in question is null.

Hope that works.

Good luck with it.

Andy

 

[sha7jpm]

Success!

andy

cheers for all the pointers,

it now works fine, although one thing I ralised is that you have to accomadate for the checkbox to be false in the syntax otherwise the resulting query does not show up any data.

many thanks for all your help.

John