Dim db As Database
Dim rst As Recordset
Dim sglAnimalSpecies As Single
Dim strAnimalSpecies As String
Dim strAnimalName As String
Dim strSQL As String
Set db = CurrentDb()
Set rst = db.OpenRecordset(“tblAnimalIntake”, dbOpenDynaset)
On Error GoTo cmdSearchAnimal_ClickErr
Select Case optSpeciesToSearch
Case 1
‘Display records for species dog.
strAnimalSpecies = “Dog”
strSQL = “SELECT AnimalID AS [Animal ID], RabiesTagNo AS [Tag No], MicrochipNo AS [Chip No], Name ”
strSQL = strSQL & “FROM tblAnimal ”
===> strSQL = strSQL & “WHERE Name Like [Forms]![frmSearchAnimal]![txtFindName] & ” * ” AND Species = strAnimalSpecies;”
Case 2
‘Display records for species Cat
Case 3
‘Display records for species Other
End Select
cmdSearchAnimal_ClickDone:
Exit Sub
cmdSearchAnimal_ClickErr:
Select Case Err.Number
Case Else
MsgBox “Error #” & Err.Number & “–” & Err.Description
End Select
Resume cmdSearchAnimal_ClickDone
End Sub
I have the above code. When I try to run it I always get snag the point marked ===>. It has something to do with the way I specify the SQL string. In the tblAnimal, the field Species is a textfield (Dog, Cat, Other). However, on my form I have used the option button to let my users select a Species i.e. 1 for Dog, 2 for cat etc. How can I modify the above code to make sure that when I run strSQL, it will translate option 1 to Dog, option 2 to Cat to match the contents of tblAnimal.
