Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Type Mismatch error in query string

    Hello, it has been some time since I last used Access.

    I have a query string which works fine in a Query, but I need to use the code with a command button on-click event and I am getting a Run Time Error 13 Type Mismatch error because the field "businessID" is numeric, and the field "businessName" is text.

    I cant remember how to fix the Type Mismatch error from the code below in VBA -

    Code:
    Dim strRowSource2 As String
    strRowSource2 = "SELECT tblBusinesses.businessID, tblBusinesses.businessName FROM tblBusinesses" & _
    "WHERE (((tblBusinesses.businessID) Like " * " & [Forms]![frmSearch]![Text5] & " * ") AND ((tblBusinesses.businessName) Like " * " & [Forms]![frmSearch]![Text5] & " * "));"
    Can anyone show me what I need to change please?

    Many thanks,

    Tom
    Last edited by moss2076; 06-05-13 at 06:15.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try:
    Code:
    WHERE (((CStr(tblBusinesses.businessID)) Like...
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    my first guess would be thart you need to quote text literals
    after that I'd guess the wildcard should have no leading or trailing sapce to the text you are searching for
    Dim strRowSource2 As String
    Code:
    strRowSource2 = "SELECT businessID, businessName FROM tblBusinesses" & _
    " WHERE businessID Like " '*" & [Forms]![frmSearch]![Text5] & "*' "  & _ 
    " AND .businessName Like " '*" & [Forms]![frmSearch]![Text5] & "*';"
    10 tips for using wildcard characters in Microsoft Access criteria expressions | TechRepublic

    however its tricky to diagnose as you haven't supplied the actual SQL that is throwing the fault, but the VBA that creates the SQL that has the fault in. usually its easier to diagnose the problem if you examine trhe actual SQL. eitrhe by seting a breakpoint and stepping through the code or at worst use a msgbox to show the variable.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Hello thank you for replying, but I still cannot work it out.

    The SQL works fine from within an Access Query -
    Code:
    SELECT tblBusinesses.businessID, tblBusinesses.businessName
    FROM tblBusinesses
    WHERE (((tblBusinesses.businessID) Like "*" & Forms!frmSearch!Text5 & "*") And ((tblBusinesses.businessName) Like "*" & Forms!frmSearch!Text5 & "*"));
    But I am using the code in an on-click event to act as the control source of a listbox to display values based on what is entered into a textbox called "Text5" at a given moment, so this is why I have created this code based on the SQL query -
    Code:
    Dim strRowSource2 As String
    strRowSource2 = "SELECT tblBusinesses.businessID, tblBusinesses.businessName FROM tblBusinesses" & _
    "WHERE (((tblBusinesses.businessID) Like " * " & [Forms]![frmSearch]![Text5] & " * ") AND ((tblBusinesses.businessName) Like " * " & [Forms]![frmSearch]![Text5] & " * "));"
    But I get the type mismatch error because businessID is numeric and businessName is text.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The Like Operator, in VBA, compares two Strings.

    businessID is Numeric, hence Sinndho's suggestion to try converting it to a String using the CStr() Function. Not sure whether it will work or not, but definitely worth a try!

    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

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Moreover, if businessName is of string type, it should be:
    Code:
    AND ((tblBusinesses.businessName) Like "'*" & [Forms]![frmSearch]![Text5] & "*'"));"
    Have a nice day!

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    Moreover, if businessName is of string type, it should be:
    Code:
    AND ((tblBusinesses.businessName) Like "'*" & [Forms]![frmSearch]![Text5] & "*'"));"
    If I insert the extra ' (apostrophes) the code becomes commented out in green.

    So the code below is what I currently am trying to make work, I am now getting no error messages at all, just an empty listbox when I click on the command button which contains this code -

    Code:
    Dim strRowSource2 As String
    
    strRowSource2 = "SELECT businessID, businessName FROM tblBusinesses WHERE (((CStr(tblBusinesses.businessID)) Like '*" & [Forms]![frmSearchTest]![Text5] & "*' AND ((tblBusinesses.businessName) Like '*" & [Forms]![frmSearchTest]![Text5] & "*'));"
    
    Me.List0.RowSource = strRowSource2
    Me.List0.Requery

Posting Permissions

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