Using check box to exclude records

[uplink600]

Good Afternoon

I have a custom pop up form set up as a query dialog on which a user will enter or select certain criteria for a search query. This is done using text boxes and combo boxes.

How do I use a check box on the form to exclude certain records from the search.

For example tick to exclude all records that have "Live" in the status field of the table that is being queried.

Thanks

VC

 

[llkhoutx]

Build a sql query to run with the criteria determined by the checkbox value.

 

[uplink600]

llkhoutx

Thanks

I know what I need done, I'm just not sure how to ammend the query or the SQL. The SQL for this query is quite involved already (see enc word doc.) Do you know SQL and can you advise.

I basically have a field in my table called "Project Status". So on the form when the user checks the box called "Live Projects Only", the query should only return all records that have "Live" in project status field of the table, along with any other criteria the user entered.

Please look at the screen shot on the enclosed doc, this should make it clearer.

Thanks for your help.

VC

 

[llkhoutx]

Add the following to the end of your WHERE clause, before ORDER BY

" AND tbName.[Project Status] Like & chr(34) & "*Live*" & chr(34) & " "

Note that with this method 'Live' can appear anywhere in the string.

I suggest that you stop using spaces in your field names. Using them dramattically complicates coding.

 

[uplink600]

llkhoutx


Thanks, I have edited the SQL and the query now returns no records if I run the search after checking the box. Can you edit my SQL and return as an attachment and I'll try that. Otherwise do you think it would be easier to use a combo to select this.

Thanks

VC

 

[llkhoutx]

My additioon to your SQO string was wrong. I suggested

" AND tbName.[Project Status] Like & chr(34) & "*Live*" & chr(34) & " "

It should be

" AND tbName.[Project Status] Like & chr(34) & "*Live*" & chr(34) & " " & _

Adding what I originally proposed would result in an SQL error. That's why your query returned no records. One has to end the line with a concantenator and the continuation symble. My mistake.

As for doing this in a combo box, I think not. You'd still have to test. Just test with what your have, e.g. the checkox. Use a test on the checkbox for True (as check boxes have three values, Null, True & False and Null is not the same as FALSE) to use your original query or with the query with my suggested modification, as appropriate. Checking for True always works.