access 2007 unwanted query results based on criteria

[blee]

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"));

 

[Allen Browne]

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"))