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

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

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