multiple checkbox arguments in query

[Jerome]

Hello,

I have a data-table that is filled with data (is logical ). I use a search form to populate data from this table into a listbox.

At the moment I have two combo-boxes (one for the column to search in and one for the column to order on). I use a textbox (sText) to enter a search string.

In the data-table there are a few columns (15) that have the data type : Yes/No
I would like to add a few check-boxes (15) where the uses can apply a pre sort.

So when the user clicks on a checkbox all the rows are selected and with the textbox a search term can be entered to search the selection. I've got this already working with one checkbox.

If ChkMember.Value = True Then
                   
            StrSql = StrSql & "WHERE " & " [Member]=True AND " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboCategory.Column(1) & ";"
                   
            Else
            
            StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ";"

            End If

Now I would like to do this with multiple check boxes, but then i becomes a problem to me....
In order to get this working (with my knowledge) I have to program every possible combination of checkbox.value = true.

Now I would like to add the checkbox "Chksubscription" with the following field-name: subscription (this is just an example for a field-name, not the real one I use). The code will be:

If ChkMember.Value = True and ChkSubscription.Value = True  Then

'Do code

elseif ChkMember.Value = True and ChkSubscription.Value = false then

'Do code

elseif ChkMember.Value = false and ChkSubscription.Value = true then

'Do code

elseif ChkMember.Value = false and ChkSubscription.Value = false then

'Do code

end if

This is the code for two check-boxes, and I have got 15 checkboxes.

Is there a better, more efficient way?

Thanks in advance.

 

[Jerome]

I know this is probably basic programming. Is there a better workaround? The problem also is the Sql statement is different withe every combination.

Thanks

 

[Jerome]

Is my question not clear or is there no solution?

I will try to explain it again:

I have a datatable with about 50 columns and 2000 rows. I search this table using a "search" form. In a textbox the user can enter a search term and select a column where to search in. The data that meets the search criteria are populated in a listbox.

Now I would like to add about 15 checkboxes to the search form. These checkboxes are linked to columns in the datatable withe the datatype: yes/no.

When a checkbox is checked (true) the user automatically search within the rows that have Yes in the column where the checkbox is linked to.

For example there is a column "subscription". Now I would like to search for every person with a first name "John" int the column first name that has got a subscription.

When the checkbox supscription is checked all the records with subscription = yes are filtered. Then the name john is entered in the text box and all the people with the first name John are displayed.

The problem is that the form has multiple checkboxes that can be checked simultaneously. For example "pre" search form subscription = yes and male = yes and search for John in this selection.

I have two questions:

1. is it possible to implement this kind of presort? and what is the most efficient way?

2. How do I deal with multiple checkboxes. How do I make the pre search SQL string? This because I have to make some kind of algorithm the checks which checkboxes are checked.

Thanks in advance for all the help, I really appreciate it.

Jerome.

 

[Jerome]

Sorry double post....

 

[Desafinada]

What about evaluating each checkbox like this (just a pseudo code)
The idea is to divide the sql string into three parts and manipulate each part accordingly. For the where clause part you can iterate all the check boxes and prolongs the clause with "AND column1 = true" for each checkbox checked.

dim strWhereClause as string 
'strWhereClause should look like "where name like "Zen" and subscribes = true and alive = true and childless = true..."
dim strSearchFor as String
'strSearchFor should look like "select * in table "
dim strOrderBy as String
'strOrderBy should look like "Order by carNumber"

str whereclause = "WHERE " & combobox(1) & "like " & textbox
For each checkBox in Form
   if value = true then
        strwhereclause = strWhereClause & "And "  & checkbox(i) & " = True"
   end if
strSQL = "strSearchFor" & strwhereclause & strOrderby