Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: How to Query Access from Excel VBA using Like

    I can't figure out why my Excel 2003 VBA query to an ACCESS data base comes back empty. Are there restrictions to using "Like" from Excel to Access?
    Code:
    'this does not work--query comes back empty
        sQRY = "SELECT Data1" & _
            " FROM " & tblName & _
            " WHERE (((Data1) Like " & """" & "*" & Word1 & "*" & """" & _
            " And (Data1) Like " & """" & "*" & Word2 & "*" & """" & "))"
    and the above code evaluates to:
    Code:
    SELECT Data1 FROM Table1 WHERE (((Data1) Like "*CAT*" And (Data1) Like "*MOUSE*"))
    The above code does what it is supposed to in a new Access query, inside the data base.
    The Data1 field contains "CAT DOG MOUSE".
    I can attach sample files if needed.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on the face of it the SQL looks OK, but its impossible to be certain as you would need to run it against

    have you run this query in Access and do you get any rows returned?
    it could be that the JET database is set up for ANSI SQL compatablility mode
    so replacing the * with a % may fix it.

    check there are actually rows that could be returned
    check if capitalisation is an issue
    what error message if any are being set.
    is data1 numeric or text
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The percent symbol worked in an Excel 2003 VBA query to an Access data base.

    Using the asterisk with Like, which does not return query results from Excel VBA, I copied the query statement from the Excel immediate window,
    then pasted it into a new query in the Access data base, ran it and it returned the expected data.
    Using the percent symbol with Like, which does return query results from Excel VBA, I copied the query statement from the Excel immediate
    window, then pasted it into a new query in the Access data base, ran it and it did not return anything.

    Thanks for the solution. The files are attached to demonstrate both scenarios. Can you add any comments about what appears to be inconsistant VBA
    syntax between Excel 2003 and Access. My O/S is Windows 7.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im confused
    has switching to ANSI compatible wild cards fixed this? ie like '%CAT%'

    Bear in mind that you are actually talking to a JET database NOT Access. Access is the front end designer the forms, reports and so on. there may well be a configuration setting in the version Excel you are using that defautls to ANSI SQL rather than JET SQL

    As to doing comparisons between EXCEL and Access VBA, don't hold your breath on that coming from me. Im reasonably up to speed in Access but tend to use spreadsheets for simple data analysis
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Yes, the switched fixed the problem. My intent with the attached files was to show both syntax scenarios, one query working and the other did not.
    I am exploring querying an Access memo field from an Excel app, and the memo field has anywhere from a short sentence to up to 2,300 characters,
    then splitting the results, if needed, for display in Excel printpreview. This is a bible search app and the Access text spans verses until a sentence end is reached.

    Thanks again. Your solution worked.

Posting Permissions

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