Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistent results with Query
I am retrieving data from a Visual Foxpro database, via ODBC using a VBA
macro to populate a worksheet. I wish to select all records which match a (slightly complex) set of criteria for an item code. Either 6-digits * I'm using LIKE '??????' to achieve this or beginning with S but not SC * I'm using (LIKE 'S%' and NOT LIKE 'SC%') The actual boolean expression is: WHERE (cn_ref LIKE '??????' or (cn_ref LIKE 'S%' and NOT cn_ref LIKE 'SC%')) When I use this expression in MS query (or via VBA) I only get records that match the second condition but when I use this from MS Access (2007), I get the full set of records I expect. I notice that in MSQ the SQL statement is re-written slightly but I can't see this being the problem. I even tried rewriting the query as the union of two individual select statements to see if MS Query could do as I ask but again, I only got the second set of data. This wasn't because the union was a sensible way to retrieve this data but because I couldn't see why two MS products failed to return the same data set. Does anybody have any ideas either how to change the logic in my where clause or achieve the results I'm after in a simpler way that MS Query can understand? Thanks, Ian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistent results with Query
does this help?
from http://support.microsoft.com/kb/225048 DAO Character Function * Match any string ? Match any character # Match any digit [a-cf] Match any of 'a' through 'c' or 'f' [~a-c] Match anything but of 'a' through 'c' ADO exposes the following ANSI wildcard characters: Character Function % Match any string _ Match any character "imillicit" wrote: I am retrieving data from a Visual Foxpro database, via ODBC using a VBA macro to populate a worksheet. I wish to select all records which match a (slightly complex) set of criteria for an item code. Either 6-digits * I'm using LIKE '??????' to achieve this or beginning with S but not SC * I'm using (LIKE 'S%' and NOT LIKE 'SC%') The actual boolean expression is: WHERE (cn_ref LIKE '??????' or (cn_ref LIKE 'S%' and NOT cn_ref LIKE 'SC%')) When I use this expression in MS query (or via VBA) I only get records that match the second condition but when I use this from MS Access (2007), I get the full set of records I expect. I notice that in MSQ the SQL statement is re-written slightly but I can't see this being the problem. I even tried rewriting the query as the union of two individual select statements to see if MS Query could do as I ask but again, I only got the second set of data. This wasn't because the union was a sensible way to retrieve this data but because I couldn't see why two MS products failed to return the same data set. Does anybody have any ideas either how to change the logic in my where clause or achieve the results I'm after in a simpler way that MS Query can understand? Thanks, Ian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistent results with Query
Thanks for your comment.
It's not the wildcard characters I have problems with its that MS Query (and hence a request over ODBC to a VFP database using VBA) cannot work with a two-part boolean WHERE statement, returning only the records that match the second. I think I'll deal with this the long way round and send two queries for simplicity. "HatesIT" wrote: does this help? from http://support.microsoft.com/kb/225048 DAO Character Function * Match any string ? Match any character # Match any digit [a-cf] Match any of 'a' through 'c' or 'f' [~a-c] Match anything but of 'a' through 'c' ADO exposes the following ANSI wildcard characters: Character Function % Match any string _ Match any character "imillicit" wrote: I am retrieving data from a Visual Foxpro database, via ODBC using a VBA macro to populate a worksheet. I wish to select all records which match a (slightly complex) set of criteria for an item code. Either 6-digits * I'm using LIKE '??????' to achieve this or beginning with S but not SC * I'm using (LIKE 'S%' and NOT LIKE 'SC%') The actual boolean expression is: WHERE (cn_ref LIKE '??????' or (cn_ref LIKE 'S%' and NOT cn_ref LIKE 'SC%')) When I use this expression in MS query (or via VBA) I only get records that match the second condition but when I use this from MS Access (2007), I get the full set of records I expect. I notice that in MSQ the SQL statement is re-written slightly but I can't see this being the problem. I even tried rewriting the query as the union of two individual select statements to see if MS Query could do as I ask but again, I only got the second set of data. This wasn't because the union was a sensible way to retrieve this data but because I couldn't see why two MS products failed to return the same data set. Does anybody have any ideas either how to change the logic in my where clause or achieve the results I'm after in a simpler way that MS Query can understand? Thanks, Ian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WORKDAY() results appear to be inconsistent... | Excel Worksheet Functions | |||
Formula results inconsistent. | Excel Worksheet Functions | |||
Inconsistent results with =LOOKUP? | Excel Worksheet Functions | |||
Inconsistent Array Count results | Excel Worksheet Functions | |||
Inconsistent results with function call....? | Excel Programming |