Creating a search string based on selections

[BigNasty]

Im trying to create a search function for my access application. I
would like users to be able to select values from drop down boxes. When
the user hits OK, the Openform method will open up the access form with
the required criteria based on the selections the user name. My problem
is putting the string together. If a user selects the 2nd and 3rd combo
box but not the first based on my code below i have a "AND" statement
in front of my search string.

Here is the code im using:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strTotal As String


If Not IsNull(cboLobName) Then
strWhere = "[Line Of Business]=" & cboLobName
Else
strWhere = " "

End If
If Not IsNull(cboSDLead) Then
strWhere1 = "[SD Lead] = " & cboSDLead
Else
strWhere1 = " "

End If
If Not IsNull(cboStatus) Then
strWhere2 = "[Status] = " & cboStatus
Else
strWhere2 = " "
End If

strTotal = strWhere & " " & " " & strWhere1 & " " & strWhere2
Text18 = strTotal

I would like it so that no matter what selection the user makes the
correct string is put together.

 

[Guest]

Try using something along the lines of;

Dim strWhere As String

If Not IsNull(Me!cboLobName) Then
strWhere = "[Line Of Business]='" & Me!cboLobName & "'"
End If

If Not IsNull(Me!cboSDLead) Then
strWhere = strWhere & " AND [SD Lead]='" & Me!cboSDLead & "'"
End If

If Not IsNull(Me!cboStatus) Then
strWhere = strWhere & " AND [Status]='" & Me!cboStatus & "'"
End If

If Len(strWhere) > 5 Then
If Left(strWhere,5) = " AND " Then
strWhere = Right(strWhere,Len(strWhere)-5)
EndIf
EndIf

I've assumed that each of the 3 fields are text values and surrounded them
with single quotes, if there numeric fields then the single quotes should be
removed.

Hope this helps.

 

[Guest]

Try it like this. Be aware that all your conditions are being constructed as
if all the fields you are comparing against are numeric. If any are string,
you need to enclose them in quotes. Here is an example of one in case you
need to change them to string:

strWhere = "[Line Of Business] = '" & cboLobName & "'"


Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim strWhere As String

If Not IsNull(cboLobName) Then
strWhere = "[Line Of Business] = " & cboLobName
End If

If Not IsNull(cboSDLead) Then
if Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[SD Lead] = " & cboSDLead
End If

If Not IsNull(cboStatus) Then
if Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Status] = " & cboStatus
End If

Text18 = strWhere

 

[BigNasty]

Awesome, thanks a lot for your help.