Two Queries with two date fields??? (1 Viewer)

[zacharyjunior]

I seem to be stumped. I have two queries that I need to generate a report with. The first query has a field complaint date, and the second query has an invoice date. I need the dollars associated for a particular time frame.

Complaint Date - What cost is associated during that time.
Invoice date - What invoices were paid during this time.

My question is how do I query against two diffrent dates?

Thanks

 

[Pat Hartman]

Assuming that the date range will be the same for both dates, something like the following should work:

Where ComplaintDate Between [Enter Start Date] And [Enter End Date] OR InvoiceDate Between [Enter Start Date] And [Enter End Date];

Your request seemed to treat these dates independantly so I used the OR relational operator. This will return a row if EITHER a complaint was made during the time period or an invoice was generated.

If you want only invoices with complaints, use:

Where ComplaintDate Is Not Null AND InvoiceDate Between [Enter Start Date] And [Enter End Date];

 

[zacharyjunior]

Sorry for the double post. Pat, where do you put this line at in the query?

Where ComplaintDate Between [Enter Start Date] And [Enter End Date] OR InvoiceDate Between [Enter Start Date] And [Enter End Date];

 

[Pat Hartman]

In the QBE grid -
put - Between [Enter Start Date] And [Enter End Date] - in the criteria line for ComplaintDate

and put - Between [Enter Start Date] And [Enter End Date] - in the second criteria line for InvoiceDate.

They need to be on separate lines so that Jet will connect the criteria with the OR operand when it creates the where clause. If both of the criteria are placed on the same line, the two clauses will be connected with the AND operand.

 

[zacharyjunior]

Pat,

I am still having a problem. I understand the part of entering the Between [Start Date] and [End Date] in the criteria part of each date. This is not a problem. The problem is that the correct data is only returned for the Invoice cost. The order total $ has the same amount and the order date is the same.

Any suggestions?

 

[GinaO]

Hi,

You could try writing two separate queries and then doing a union of both.

I have the same problem & this kinda worked out.
Hope this helps .

Gina.