Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Question Unanswered: Help with "DATA TYPE MISMATCH IN CRITERIA EXPRESSION" Error

    Ok, so I have read other forum posts on similar issues but none seem to help me out.

    Here is the error i am getting and the equivalent code:

    Code:
    sql = calculateSQL
        sql2 = calculateDisplaySQL
     
        If sql = "" Then
            MsgBox ("No criteria selected")
        Else
           'call report here
           DoCmd.OpenReport "Everything Report", acViewPreview, , sql, acWindowNormal, sql2
       
        End If
    To make it easier to understand, in the instance I am running it in and it is failing,
    sql = "AdminID='4' AND (Committee=3) AND (ClosedReason=1)"
    sql2 = "AdminUnit='Engineering' AND (Committee=IRB) AND (ClosedReason=Protocol Currently Active)"
    And from the database,
    AdminID is an int
    Committee is an smallint
    ClosedReader is an smallint
    When I debugged into the DoCmd.OpenReport, it takes me to a function which basically does the following:

    Code:
    Private Sub Report_Open(Cancel As Integer)
        lblFilter.Caption = Me.OpenArgs
    End Sub
    where Me.OpenArgs = sql2 from above. I also tried changing it to sql and it gives me the same error.

    If I query "AdminID='4' AND (Committee=3) AND (ClosedReason=1)" through Microsoft SQl Server Management Studio, I get 62 results. But I get this following error on the DoCmd.OpenReport line:
    Run-time error '3464';
    Data type mismatch in criteria expression.
    Can someone please tell me what is wrong and how to fix it? Thank you in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be AdminID=4 (no quotes) as AdminID is an Integer. Quotes are used for Text or Memo values, never for numeric values.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Although you may not have run into it yet, I suspect that you'll also have a problem with
    Code:
    If sql = "" Then
      MsgBox ("No criteria selected")
    Else
    My guess is that you're trying to use the value in your calculateSQL Control as a Criteria, and if nothing has been entered or selected from the Control (depending on the kind of Control calculateSQL is) you want your Messagebox to warn the user. The problem with this is that if no Criteria is entered into a Textbox or no Criteria is selected from a Combobox/Listbox, sql will not equal "" (a Zero-Length String or ZLS) but rather will be Null. To check for both ZLSs and Nulls, you need to use something like
    Code:
    If Nz(sql, "") = "" Then
      MsgBox ("No criteria selected")
    Else
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2011
    Posts
    2
    Quote Originally Posted by Sinndho View Post
    It should be AdminID=4 (no quotes) as AdminID is an Integer. Quotes are used for Text or Memo values, never for numeric values.
    Oh wow... I thought I had tried this... apparently not! jeez... can't believe I was stuck on this for 3 days before posting the issue.

    Thanks Sinndho!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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