Query to search 3 seperate 'Date' fields

[veraloopy]

I have a Car Service Table that holds 3 service dates (see screen shot called table information).
I have a query (attached screenshot) that asks for the start and end date to do a search on the 3 seperate dates so I can easily see if customers are due for a service regardless of whether it’s their 1st, 2nd or 3rd service.

However, it only searches on the 1st service date and if I put the criteria in all 3 dates on the query, it asks me for 3 lots of start and end dates.

Also, I only need results where the ‘Completed’ check box is not ticked (see table information screen shot)

So basically, I’m looking to enter only 1 start and end date which returns results only if the ‘Completed’ box is not checked.....

Any ideas???

Any help is greatly appreciated

 

[Kafrin]

Have you tried setting your dates as proper parameters?

I don't know my way around Access 2007, but in 2003 you go on the menu bar to Query, Parameters, then enter the names and set the types.

I *think* this will stop it asking for the same dates multiple times, allowing you to use your second solution.


Alternatively you have a field that looks something like:

Iif((Date1 Between StartDate And EndDate) Or (Date2 Between StartDate And EndDate) Or (Date1 Between StartDate And EndDate), 1, 0)

Criteria: =1

 

[veraloopy]

Thanks for this, I've got my code as follows:

SELECT CarDetails.[1stService], CarDetails.[2ndService], CarDetails.[3rdService]
FROM Contacts INNER JOIN CarDetails ON Contacts.CustRef = CarDetails.CustRef
WHERE (((CarDetails.[1stService])<>False)) OR (((CarDetails.[2ndService])<>False)) OR (((CarDetails.[3rdService]) Between [Enter Start Date] And [Enter End Date]));


however, it now returns all records regardless of dates....

Any ideas?

 

[Brianwarnock]

However, it only searches on the 1st service date and if I put the criteria in all 3 dates on the query, it asks me for 3 lots of start and end dates.

The way to stop this is to drive the query from a form.
The form will have a textbox for each date and a command button to run the query.

In the query you will have

Between forms!formname!starttextboxname And forms!formname!Endtextboxname

Use meaningful names and avoid spaces.
This will be entered 3 times using the or situation but as the form is open the data is available for each use, including as information on any report that is produced, say in the report header.

Brian