none
Excel running a quey from MSAccess and getting different answer

    Question

  • I have an excel sheet which has a 'connection' to an Access file, specifically a query in that file. When I run the query in Access I get one result and when I refresh in my excel I get a different answer.

    After many hours and redesigns I isolated it down to the criteria in the Access file.

    If I use ‘Like’ or ‘Not Like’ in the criteria, when Excel refreshes it can’t handle this??? Shouldn’t it just run the query from Access?

    EG

    When I used NOT LIKE *SMITH* Excel ignored it and gave me all the records

    When I used LIKE *SMITH* Access gave me 5 records but Excel none

    Does this mean I cannot trust and use this function?
    Tuesday, August 27, 2013 4:01 AM

Answers

  • Hi siicl.

    Could you please give me the whole query and any value of the field you match?

    It seems the field contains blank value or other conditions affect the result.

    Like can Compare a string expression to a pattern in an SQL expression.

    for example:

    select * from table1 where field1 like "*SMITH*"

    It will return all fields from table1 if field1 contains "SMITH".

    For more information, please look at the below article:

    Like Operator


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.




    Wednesday, August 28, 2013 3:20 PM
  • I don't have a solution for you, but I suggest you look at the characters you are using as wildcards. My guess is that the * needs to be replaced with something different when running the query from inside Excel. SQL Server queries, for example, use % as a wildcard, not *. 

    Your results are consistent with the literal value ignoring the wild cards (since you probably do not have anyone actually called "Mr. *SMITH*" !

    Possibly the query is being parsed differently depending on where it originates...

    Wednesday, August 28, 2013 4:33 PM

All replies

  • Hi siicl.

    Could you please give me the whole query and any value of the field you match?

    It seems the field contains blank value or other conditions affect the result.

    Like can Compare a string expression to a pattern in an SQL expression.

    for example:

    select * from table1 where field1 like "*SMITH*"

    It will return all fields from table1 if field1 contains "SMITH".

    For more information, please look at the below article:

    Like Operator


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.




    Wednesday, August 28, 2013 3:20 PM
  • I don't have a solution for you, but I suggest you look at the characters you are using as wildcards. My guess is that the * needs to be replaced with something different when running the query from inside Excel. SQL Server queries, for example, use % as a wildcard, not *. 

    Your results are consistent with the literal value ignoring the wild cards (since you probably do not have anyone actually called "Mr. *SMITH*" !

    Possibly the query is being parsed differently depending on where it originates...

    Wednesday, August 28, 2013 4:33 PM