Help with date query

[will206]

I work in a logistics firm have been working on a database to keep track of the vehicles we use/defects/accidents etc. The nature of the business means that we often have to hire in trailers for weeks/months at a time and then get rid of them afterwards. It is this aspect of the database I am having trouble reporting on.

The trailer details table links to a seperate table that records "Trailer ID", "Date on hire" and "Date off hire". Eg. 1 record might be:

Trailer ID: TX700
Date on hire: 07/01/2006
Date off hire: 19/04/2006

Meaning that we have trailer TX700 on hire between 07/01/2006 and 19/04/2006.

From this data I need to be able to run two queries. One where the user can enter a single date and it will return all of the trailers on hire for that particular date and one where the user can enter a date period and the database will return all trailers that were hired within that period and the dates we hired them from and to.

Any help with this problem is very much apreciated and I can give you more info/explanation if required.

 

[Jibbadiah]

Create the queries that you want.
In the first query (single date) put [enter a single date] in the criteria section.
In the 2nd query (date range) put [enter start date] in the Date on hire field, put [enter end date] in the Date off hire field.
By doing this a message will pop-up that tells the user to enter the dates. Then the query runs based on their responses.

J.

 

[Jibbadiah]

Sorry mate... I'm tripping... forgot about the major part of the syntax

for Date range input in criteria:
Date on Hire >= [enter start date]
Date off Hire <= [enter end date]

 

[will206]

Thanks for the help, I still have one big problem though. Lets say I have the record:

Trailer ID: ST550
Date on hire: 19/03/2006
Date off hire: 04/04/2006

Say the user needs to know all vehicles on hire during March and a run down of what vehicles were on hire on 31/03/2006.

For query 1 they type in 31/03/2006, however this will not return ST550 as although it was on hire on that day it is not expicitly recorded on the record.

For query 2 they type in 01/03/2006 and 31/03/2006, again this does not return ST550 as its dates do not both fall within the period of the query.

Is there a way to make the query pick up the data like this? Hopefully this explains what I need to do a bit better.

 

[Brianwarnock]

Does >=StartDate AND <= Enddate work

Brian

 

[will206]

I have both of those in the query currently, still no joy.

I suppose for the second query I need to be able to put in the two dates and if either of the 2 on the record are between them then the record is returned.

For the first query I need to be able to type in 1 date and if this is within the 2 dates on the record then the record is returned.

 

[Jibbadiah]

Sorry... have a habit of not reading posts properly. Experiment with the following to see if they work.

1.

Date on hire <=[choose date] AND Date off Hire >=[choose date]
Or Date off Hire Is Null (or default)

2.

Date on hire Between [start date] And [end date]
Or
Date off hire Between [start date] And [end date]
Or
Date on hire <=[start date] AND Date off Hire Is Null (or default date)
Or
Date on hire <=[start date] AND Date off Hire >=[end date]

This assumes that both dates are inclusive... I predict that you don't actually have the trailer on the end date, if so you will need to remove the '=' for the date off hire field.

 

[will206]

Brilliant! Thankyou very much! I have it all working now so surely a promotion is on the way!!