BuildCriteria in VBA

[Secret Squirrel]

I'm trying to build a SQL statement using only the checkboxes on my form that
are true. Here's what I have so far but the SQL is also being built with the
checkboxes that are false. How can I change the code to only have it build
the SQL with just the checkboxes that are true?

On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

Me.txtSQL = Null
'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select EmpName from tblSkills "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the check box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acCheckBox
.SetFocus
'This is the function that actually builds the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " &
BuildCriteria(.Name, dbBoolean, .Value)
End If
End Select
End With
Next ctl

'Set the listbox rowsource equal to the new select statement.
Me.txtSQL = sSQL & sWhereClause
Me.EmpList.RowSource = Me.txtSQL
Me.EmpList.Requery


Here's an example of the SQL that I have now:

What's happening is when I open my form and put a check in the checkbox
"Titanium" and "Aluminum and then run my SQL it works fine. But when I then
remove the check from the "Aluminum" box the SQL looks like this:

select EmpName from tblSkills Where Titanium=-1 And Aluminum=0

I need the above example to look like this when I remove the check from the
"Aluminum: checkbox:

select EmpName from tblSkills Where Titanium=-1


Any help would be greatly appreciated.

SS

 

[Dirk Goldgar]

I'm trying to build a SQL statement using only the checkboxes on my form
that
are true. Here's what I have so far but the SQL is also being built with
the
checkboxes that are false. How can I change the code to only have it build
the SQL with just the checkboxes that are true?

On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

Me.txtSQL = Null
'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select EmpName from tblSkills "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the check box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acCheckBox
.SetFocus
'This is the function that actually builds the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " &
BuildCriteria(.Name, dbBoolean, .Value)
End If
End Select
End With
Next ctl

'Set the listbox rowsource equal to the new select statement.
Me.txtSQL = sSQL & sWhereClause
Me.EmpList.RowSource = Me.txtSQL
Me.EmpList.Requery


Here's an example of the SQL that I have now:

What's happening is when I open my form and put a check in the checkbox
"Titanium" and "Aluminum and then run my SQL it works fine. But when I
then
remove the check from the "Aluminum" box the SQL looks like this:

select EmpName from tblSkills Where Titanium=-1 And Aluminum=0

I need the above example to look like this when I remove the check from
the
"Aluminum: checkbox:

select EmpName from tblSkills Where Titanium=-1

Any help would be greatly appreciated.

This modification ought to do it:

Case acCheckBox
If (.Value) Then
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & _
BuildCriteria(.Name, dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " & _
BuildCriteria(.Name, dbBoolean, .Value)
End If
End If

 

[Tom van Stiphout]

On Sun, 30 Aug 2009 20:30:01 -0700, Secret Squirrel

..SetFocus is not needed.

Put an IF statement in place:
case acCheckBox
if ctl.Value = True then
'add it
else
'don't
end if

-Tom.
Microsoft Access MVP