Using 2 Criterias in a Filter

[Topher]

Hi,

I'm trying to create a report which when run loads a filter which is passed in through the docmd.openreport command. it works fine if the filter has only one criteria. IE Date Between ## and ## BUT if i enlarge the filter to contain two criterias IE Date Between ## and ## AND Date2 between ## and ## the report only shows the information as if it were combining two querys, one with records from date and one with records from date2.

if i write a query with a where statement using the criteria as above only looking at the dates i want for the fields date and date2 - i GET the records i want. but this is no good since I want the users to be able to enter in any dates.

does anyone know what im doing wrong or if this is a problem in access 97?

cheers

 

[Alexandre]

I would like to see your SQL statement, but i believe you do not make (proper) use of parenthesis:

(Date Between ## and ##) AND (Date2 between ## and ##)

Alex

 

[Topher]

heres my sql statement which is written into the recordsource area of the report. i then use the docmd.openreport command with a variable filled with the filter, and placed in the filter option of the docmd. command

SELECT tblASQuotes.QuoteNum, tblCustomers.OrganizationName, tblQuoteItems.QuoteItem, tblQuoteItems.PartNum, tblQuoteItems.Date, tblQuoteItems.MinQuantity, tblQuoteItems.MaxQuantity, tblQuoteItems.ASSIPrice, tblQuoteItems.Bid, [MinQuantity]*[AssiPrice] AS MinPrice, [MaxQuantity]*[AssiPrice] AS MaxPrice FROM (tblCustomers RIGHT JOIN tblASQuotes ON tblCustomers.CustomerID = tblASQuotes.Customer) LEFT JOIN tblQuoteItems ON tblASQuotes.QuoteNum = tblQuoteItems.QuoteNum GROUP BY tblASQuotes.QuoteNum, tblCustomers.OrganizationName, tblQuoteItems.QuoteItem, tblQuoteItems.PartNum, tblQuoteItems.Date, tblQuoteItems.MinQuantity, tblQuoteItems.MaxQuantity, tblQuoteItems.ASSIPrice, tblQuoteItems.Bid, [MinQuantity]*[AssiPrice], [MaxQuantity]*[AssiPrice] HAVING (((tblCustomers.OrganizationName) Like "*Topeka*") AND ((tblQuoteItems.Bid)=Yes)) ORDER BY tblASQuotes.QuoteNum;

- hmm do i need to have both dates selected in the query in order for the filter work?

 

[Topher]

but i dont use a table for the recordsource i use that query.

i could create a query using vba everytime the user wants to run the report. that would work for then i could add the dates into the query itself rather than using a filter. is that what you mean?

 

[KKilfoil]

Could the problem be related to the use of 'Date' as a parameter/fieldname.

I thought 'Date' was reserved. You might try it using [Date1] instead!

 

[Topher]

i didnt even see that! thanks! i'll give that a try. i was able to get it working turns out my filter wasn't setup properly in the first place - but its still doing something weird and that might be it.

cheers