An SQL str using IF condition

[spnz]

Good morning


I am hoping some one can help me out with an SQL string I am having problems during to get working.

This is my code

Private Sub SearchRoleSeeking()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strFilterSQL As String
    Dim txtBoxValue As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearch")
    
   lstSearchResults = txtSearch1
    
    If Me.txtSearch1.Value = "Temp" Then
    txtBoxValue = 1
    End If
    If Me.txtSearch1.Value = "Perm" Then
    txtBoxValue = 2
    End If
    If Me.txtSearch1.Value = "Temp or Perm" Then
    txtBoxValue = 3
    End If
     strSQL = "SELECT tblPersonalInformation.[PersonalID],tblPersonalInformation.[Surname],tblPersonalInformation.[Forename],tblPersonalInformation.[DOB],tblPersonalInformation.[WantedRate],tblPersonalInformation.[WantedSalary],tblPersonalInformation.[Status],tblPersonalInformation.[RoleSeeking]" & _
                "FROM tblPersonalInformation " & _
                "WHERE tblPersonalInformation.RoleSeeking = ('txtboxvalue')"
                
    
    qdf.SQL = strSQL
    Me.lstSearchResults.RowSource = "qrysearch"
    
    Set qdf = Nothing
    Set db = Nothing

End Sub

let me try explain what I am trying to do.
I have a form where personal information is entered into. It has an option group that allows 3 choices.
I have made a form that I will use to search (the above code is from that.) What I am trying to do is if a person enters "Temp" into txtSearch1 then for the SQL statement to search "tblPersonalInformation.[RoleSeeking]" for the value 1.
If a person enters "PERM then for the statement to use 2 as the value for txtSearch1 and so on.

Can anyone help out with my task at hand.

Thanks everyone for your help.

 

[Jon K]

"WHERE tblPersonalInformation.RoleSeeking = " & txtBoxValue

If [RoleSeeking] is a text field, surround the value with single quotes:-
"WHERE tblPersonalInformation.RoleSeeking = '" & txtBoxValue & "'"


Note

When the Row Source Type of a list box is Table/Query, you can directly pass the SQL string to the RowSource of the list box. This code should also work:-

Public Sub SearchRoleSeeking()
    Dim strSQL As String
    Dim iBoxValue As Integer

    If Me.txtSearch1.Value = "Temp" Then
       iBoxValue = 1
    End If
    If Me.txtSearch1.Value = "Perm" Then
       iBoxValue = 2
    End If
    If Me.txtSearch1.Value = "Temp or Perm" Then
       iBoxValue = 3
    End If
    
    strSQL = "SELECT [PersonalID],[Surname],[Forename],[DOB]," & _
             "[WantedRate],[WantedSalary],[Status],[RoleSeeking] " & _
             "FROM tblPersonalInformation " & _
             "WHERE [RoleSeeking] = " & iBoxValue
            
    Me.lstSearchResults.RowSource = strSQL
End Sub