Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Post Unanswered: Passing Query Definitions

    I have used the following code, found on the web, to successfully query data based on strings only. On a form, a user can choose criteria to search using three drop down menus. If the user leaves a drop down menu null, the code will return all values from that column. I'm having trouble when querying numeric values. I'll receive an "Error 13 type mismatch" message. The issue occurs when I try to pass Like '*' to retrieve all values. If there is a value in all three drop down menus, the query works fine. I have tried to manipulate the "Like" code but I still cannot get it to work. I'm using Access 2002 on Windows XP Professional. Here is the code and thanks for your help:

    Dim db As DAO.Database
    Dim RS As Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim FullName As Integer, JobCode As Integer
    Dim Auditor As Integer

    ' Identify the database and assign it to the variable
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_Research_Search")
    Set RS = db.OpenRecordset("tbl_Research_Audits", dbOpenDynaset)

    ' Get values from the combo boxes
    If IsNull(Me.txtEmployeeID.Value) Or Me.txtEmployeeID.Value = "" Then
    FullName = " Like '*' "
    Else
    FullName = txtEmployeeID.Value
    End If
    If IsNull(Me.txtJobCodeID.Value) Or Me.txtJobCodeID.Value = "" Then
    JobCode = " Like '*' "
    Else
    JobCode = txtJobCodeID.Value
    End If
    If IsNull(Me.txtAuditorID.Value) Or Me.txtAuditorID.Value = "" Then
    Auditor = " Like '*' "
    Else
    Auditor = txtAuditorID.Value
    End If

    ' Build the SQL string
    strSQL = "SELECT tbl_Research_Audits.* " & _
    "FROM tbl_Research_Audits " & _
    "WHERE tbl_Research_Audits.[Research Rep] = " & FullName & _
    " AND tbl_Research_Audits.Auditor = " & Auditor & _
    " AND tbl_Research_Audits.[Type of Inquiry] = " & JobCode & _
    " ORDER BY tbl_Research_Audits.Date;"

    ' Pass the SQL string to the query
    qdf.SQL = strSQL

    ' Turn off screen updating
    DoCmd.Echo False

    ' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qry_Complete") = acObjStateOpen Then
    DoCmd.Close acQuery, "qry_Research_Search"
    End If
    ' Open the query
    DoCmd.OpenQuery "qry_Research_Search", acViewNormal, acEdit

    cmdSearch_Click_exit:
    ' Turn on screen updating
    DoCmd.Echo True
    ' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

    Err_cmdSearch_Click:
    'Handle Errors
    MsgBox "An unexpected error has occurred." & _
    vbCrLf & "Please note of the following details:" & _
    vbCrLf & "Error Number: " & Err.Number & _
    vbCrLf & "Description: " & Err.Description, vbCritical, "Error"

    Resume cmdSearch_Click_exit

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I think the trouble is that you're trying to pass string criteria for a numeric type.

    If your user doesn't select anything, essentially saying return all results, why put criteria in at all? Just leave it out of the WHERE criteria all together.
    Me.Geek = True

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Also I don't think you canhave
    "WHERE tbl_Research_Audits.[Research Rep] = Like '*'"

    Which is what you have when txtEmployeeID is null

    It should be

    "WHERE tbl_Research_Audits.[Research Rep] Like '*'"

    ??


    MTB

Posting Permissions

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