Multiple criteria problem

[DataBug]

I have a query that I want to apply 2 separate criteria to.

"Date" = Between startdate and enddate AND "hazmat" = True.

For some reason it isn't working. Date is a date field and hazmat is a yes/no field. The query returns all records between start and end date and flags the hazmat field =true.

I know that the way I'm doing this worked in Access 97 but apparently it won;t in Access 2000.

Thanks

 

[pdx_man]

If your fieldname is 'Date', then you will have problems as that is a reserved function that returns the system date. Change the fieldname to something else, for starters.

Are you looking for an OR condition, perhaps? Where it is between two dates OR hasmat is true?

 

[DataBug]

Thanks.

Actually, the name of the field is "Date Moved".

What I need the query to return is records between startdate and enddate AND Hazmat = True.

It is returning the proper records as far as the date range is concerned. It's also returning ALL the records rather than just the ones where hazmat = true.

Here's the SQL :

SELECT Billing.[Date Moved], Billing.[Proper Hazmat Name], Shipper.Shipper, Shipper.Scity, Consignee.Consignee, Consignee.Ccity, Billing.[UN Number], Billing.[Packing Group], Billing.HazMat
FROM Billing, Shipper, Consignee
WHERE (((Billing.[Date Moved])>=([Forms]![Date]![StartDate]) And (Billing.[Date Moved])<=([Forms]![Date]![EndDate])) AND ((Billing.HazMat)=True));

 

[pdx_man]

Try:
((Billing.HazMat)=-1));

 

[DataBug]

Nope, that didn't work. Still returned all records.

Seems like it has something to do with it being a double criteria because I can create a query with either criteria by itself and it works.

 

[DataBug]

UPDATE :

It was a problem with the table relationships. Connected the dots now all is well.

Thanks