access 2007 unwanted query results based on criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blee - 12 Mar 2008 21:41 GMT

Using the statement below, the criteria for Cust_Id1 field yields "00001"
records as well as other records not = to 00001. This is a text field. I
would expect all Cust_Id1 records not = 00001 to be filtered out
Strangely when I run the same query in Access 2002/2003 the criteria is
succesful in filtering the records as desired.

See sql below, Thanks

SELECT dbo_vw_HH_Trx_History.Type, dbo_vw_HH_Trx_History.Trx_Date,
dbo_vw_HH_Trx_History.Date_Applied, dbo_vw_HH_Trx_History.User_Name,
dbo_vw_HH_Trx_History.Direction, dbo_vw_HH_Trx_History.Average_Fill,
IIf([dbo_vw_HH_Trx_History]![Direction]="-",[dbo_vw_HH_Trx_History]![Average_Fill]*-1,[dbo_vw_HH_Trx_History]![Average_Fill])
AS [actl qty], dbo_vw_HH_Trx_History.SpiritCategory,
dbo_vw_HH_Trx_History.Bi_Reference, dbo_vw_HH_Trx_History.Ifs_Reference,
dbo_vw_HH_Trx_History.Cust_Id1
FROM dbo_vw_HH_Trx_History
WHERE (((dbo_vw_HH_Trx_History.Date_Applied)>#10/1/2007# And
(dbo_vw_HH_Trx_History.Date_Applied)<#1/1/2008#) AND
((dbo_vw_HH_Trx_History.Direction)<>"0") AND
((dbo_vw_HH_Trx_History.SpiritCategory)<>"BRANDY" Or
(dbo_vw_HH_Trx_History.SpiritCategory)="RUM") AND
((dbo_vw_HH_Trx_History.Cust_Id1)="00001"));

Reply to this Message

Allen Browne - 13 Mar 2008 01:59 GMT

Might be due to the mix of Ands and Ors (though it looks okay at a glance.)

In any case, you don't need the:
OR SpiritCategory = "RUM"
as well as:
SpiritCategory <> "BRANDY"
If it's rum, that's different to brandy, so the rum adds nothing.

Try replacing the WHERE clause with this:
WHERE ((dbo_vw_HH_Trx_History.Date_Applied > #10/1/2007#)
AND (dbo_vw_HH_Trx_History.Date_Applied < #1/1/2008#)
AND (dbo_vw_HH_Trx_History.Direction <> "0")
AND (dbo_vw_HH_Trx_History.SpiritCategory <> "BRANDY")
AND (dbo_vw_HH_Trx_History.Cust_Id1 = "00001"))

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Using the statement below, the criteria for Cust_Id1 field yields "00001"
> records as well as other records not = to 00001. This is a text field. I
[quoted text clipped - 18 lines]
> (dbo_vw_HH_Trx_History.SpiritCategory)="RUM") AND
> ((dbo_vw_HH_Trx_History.Cust_Id1)="00001"));

Reply to this Message