Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Translate query into SQL (Access 2K)

    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.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Translate query into SQL (Access 2K)

    try changing
    strSQL & "WHERE Name Like" & [Forms]![frmSearchAnimal]![txtFindName] & " * " AND Species = strAnimalSpecies;"


    the piece [Forms]![frmSearchAnimal]![txtFindName] is in quotes and being treated as a string, when it is really pointing to text in a textbox
    HTH

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translate query into SQL (Access 2K)

    Try this :
    <pre>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 & "'"
    MsgBox strSQL</pre>

    Look at the message box and check if the txtFindName and strAnimalSpecies are replaced with the wanted text and that they are surrounded by '
    Once your query runs ok, you can remove the line MsgBox strSQL
    Francois

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translate query into SQL (Access 2K)

    ZAve,
    You have to surround the entries by ' as they are text fields
    Francois

  5. #5
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translate query into SQL (Access 2K)

    I tried and it works! Rock on, guys!

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Translate query into SQL (Access 2K)

    you are correct

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •