How can i do this... seems very complex

[mtrack81]

I have four combo boxes on a form and a certain item from each combo box is to be selected. From the code below it works fine when all of the combo boxes have a selection, but how do i work around when only some of the boxes are selected or either none of them are selected. I know i should use the IsNull function but can't seem to come up a good idea of manipulating this.. by the way. "TableName" is a variable..

strSQL = "SELECT * FROM " & TableName & " WHERE" _
& " Project_Name = '" & ProgCombo.Column(0) _
& "' AND LRU = '" & LRUCombo.Column(0) _
& "' AND CSCI = '" & CSCICombo.Column(0) _
& "' AND Category = '" & CategCombo.Column(0) _
& Chr(39) & ""

Set rsRef = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

anyone have an idea or suggestion that can help me out? thanks in advance..

[michi]

Just a trick for me: And "1=1" at the beginning.

========
strSQL = "SELECT * FROM " & TableName & " WHERE 1=1"

If ProgCombo.ListIndex <> -1 Then
strSQL = strSQL & " and Project_Name = '" & ProgCombo.Column(0) & "'"
End If
If CSCICombo.ListIndex <> -1 Then
strSQL = strSQL & " and CSCI = '" & CSCICombo.Column(0) & "'"
End If
If CategCombo.ListIndex <> -1 Then
strSQL = strSQL & " and Category = '" & CategCombo.Column(0) & "'"
End If

======

Hope this helps.




Regards,

Michi