dates range in query

[berniecliffdon]

Hello all

I was wondering if anybody could help me out with a query problem I've been having.
I've been trying to use a query to display a list of available cars for a given start and end date entered by the users. I have found some guidance to make an attempt but it isn't working. The text in the Input boxes isn't what I'd like. Also the query is displaying all the cars in my database even when I deliberately trying to exclude some.
I'd really appreciate any help As this is my first database and Im really struggling with the use of criteria.
I've included a screen grab including my formulas

http://img.photobucket.com/albums/v242/b3rnie/access_screen.jpg

Apologies If I missed this advice in my searches

 

[lespaul]

So here's the data from the first image:
[tt]
Rental Number Customer Number Car Registration Start_Date End_Date Mileage Start Mileage End
2 2 ST05BDL 2/5/2007 2/12/2007 40876 0
3 1 T829THG 2/5/2007 2/8/2007 59437 59535
4 6 T829THG 1/26/2007 1/30/2007 75172 [/tt]

So now on the form I enter 2/10/2007 to search for and the query generated is (where the bolded section is what is entered on the form):

SELECT * FROM TableName WHERE [b]2/10/2007[/b] BETWEEN Table.Start_Date AND Table.End_Date

you're saying that would work?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[berniecliffdon]

If you give me the SQL I'll test the code you're talking about in my existing query if you would like.

 

[PHV]

The following query:
SELECT * FROM TableName WHERE #2007-10-02# BETWEEN Table.Start_Date AND Table.End_Date

should return the 1st row of your posted data (I assumed US date format).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

So which Table.Start_Date and Table.End_Date records does it compare too?

 

[PHV]

Sorry for the typo:
SELECT * FROM TableName WHERE #2007-02-10# BETWEEN Table.Start_Date AND Table.End_Date

2007-02-05 <= 2007-02-10 <= 2007-02-12 ? True
2007-02-05 <= 2007-02-10 <= 2007-02-08 ? False
2007-01-26 <= 2007-02-10 <= 2007-01-30 ? False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886