Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Data Type Mismatch In Query Expression

    I have a VBA function that returns a string datatype.
    When I include this function in a query and then compare it to a TEXT datatype column in a query, I get the error message "Data Type Mismatch In Query Expression".

    I get this even if I wrap the function results and the text column reference in the CSTR function.

    So, essentially, I get a data type mismatch error when comparing a string to a string.

    What gives? Any ideas?

    By the way, I've been using this function for more than ten years, and never had any problem before. Possibly an issue with Access 2007?
    Last edited by blindman; 07-07-10 at 14:02.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Sounds like there could be an issue with null values in one of the fields. Try using nz() to convert nulls to an empty string and see if that solves the problem.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good suggestion, but did not help.

    I get this error even when limiting the result set to a dozen records that I know are not nulls, and that I know do not contain any reserved characters.

    I also get it when I compare the function output to a simple hard-coded string in quotes. But if I export both the string and the function output to a temporary table, both columns are created as text(255).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I dumbed my function down to this:

    Code:
    Function FormatName2(NameString As String, NameFormat As String) As String
    FormatName2 = "The Blindman"
    End Function
    Yet when I put this function in a query and try to compare the results to the text string "The Blindman", I get the data type mismatch error.

    Code:
    SELECT Consolidated.StudentName
    FROM Consolidated
    WHERE (((Consolidated.StudentName)="The Blindman") AND ((formatname2([StudentName],"F M L S"))="The Blindman"));
    This is totally bogus.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Found the issue, but very strange.

    Wrapping the results of the function in NZ() did nothing, but wrapping the submitted parameter solved the problem.

    What is strange (and shamefully sad) is that I included criteria to omit NULL parameters from the result set, but apparently this clause is not applied until AFTER the function is applied to every row in the table.

    So I was getting this error even though none of the parameters were actually NULLs.

    Very lame, Microsoft.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    glad you got it sorted

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Your solution works, or an alternate solution is to declare your variables as type Variant in your function. Then within the function you can check to see if it is Null or an empty string and proceed accordingly. It is only slightly more 'clean' when all of the logic is in the function.

Posting Permissions

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