Build WHERE Clause

[Gsurfdude]

Hello,

I have 4 combo box dropdowns and I need to build a WHERE clause based on any
combination of them or all using VBA. Can someone show me or point me in the
right direction?

Thanks.

 

[KARL DEWEY]

I think this will get you started.
Add a checkbox in your form to select AND or OR.

SELECT [Old-New].NewID, [Old-New].OldID
FROM [Old-New]
WHERE ((([Old-New].NewID)=[Forms]![AND-OR Form]![List2] Or [Forms]![AND-OR
Form]![List2] Is Null) AND (([Old-New].OldID)=[Forms]![AND-OR Form]![List4]
Or [Forms]![AND-OR Form]![List4] Is Null) AND (([Forms]![AND-OR
Form]![check6])=0)) OR ((([Old-New].OldID)=[Forms]![AND-OR Form]![List4] Or
[Forms]![AND-OR Form]![List4] Is Null) AND (([Forms]![AND-OR
Form]![check6])=-1)) OR ((([Old-New].NewID)=[Forms]![AND-OR Form]![List2] Or
[Forms]![AND-OR Form]![List2] Is Null) AND (([Forms]![AND-OR
Form]![check6])=-1));

 

[Klatuu]

Dim strWhere As String

If Not IsNull(Me.Combo1) Then
strWhere = "[FirstField] = " & Me.Combo1 'Numeric Syntax
End If

If Not IsNull(Me.Combo2) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[SecondField = """ & Me.Combo2 & """" 'Text
Syntax
End If

If Not IsNull(Me.Combo3) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ThirdField = #" & Me.Combo3 'Date Syntax
End If

If Not IsNull(Me.Combo4) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Last Field = " & Me.Combo3 'Numeric Syntax
End If
***********
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function