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

 

[simon551]

It's kind of hard to tell what you are trying to do. When the user enters the date do you want this to filter the records for the start date in the table? or the end date? or both?

You will probably want to put the parameter based on the form entry in the criteria for the field that comes from the data.

It looks like you are trying to do the opposite (filter the data from the form based on the data in the table)

 

[Remou]

You seem to have things round the wrong way. Add the date from the table to the query grid. Put a start date and end date textbox on the form and reference the form on the criteria line. Switch to SQL view and cut and paste the SQL here if you are still having problems.

 

[BSman]

Assuming this is a database application you will want to make available to users, I would suggest the following (which will make it easier to use repeated ly):

First, make a form with controls for the start and end date (txtStartDate, txtEndDate), plus a button to display the results of your query. I'll call the form frmFindData.

Then build your query using the Access query builder. Right click the criteria cell for the date field and, using the builder end up with a criteria statement that should look something like "Between forms!frmFindData!txtStartDate AND forms!frmFindData!txtEndDate". You will have to type the word "between" in to the start of the build's query display box, but be sure to select the controls on the form by clicking on the appropriate items in the lower half of the query builder dialog.

On the form, set up the button so it opens the query. Initially you can just manually open the query once you have opened the form and entered your two dates.

You could also use the form for any other criteria you want the user to select.

Bob

 

[berniecliffdon]

Thanks for the replys everyone.

I don't think I've done a good job explaining what I want.
I want to set up a query that a user would open by clicking a link on switchboard
("Check for availability of cars for rental")
that would then open a this query. As soon as this Query opens I want them to enter start date, then end date.
Then I want these actions to display a list of all the cars that are available in this date range.

I don't nessacerly want to use forms at all. If I could just get the correct answer from the Bookings table that would be great.

I've tried to change my query to reflect this. Now I've got

Expr1: [start Date]

Between [Start Date] And [End Date]

This does give me the correct text for the input boxes I want the user to use, but it still returns all the cars in my database even when I I deliberatly try to exclude them.
I think I need to refer to the table Bookings in the criteria somewhere, But im really A bit clueless of the syntax to use.
Here's the updated screenshot.

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

It would make things clearer I could upload the database, it only has dummy data in

many thanks every one.

 

[lespaul]

why not have the link on the switchboard open a form, gather all the criteria in one place and THEN run the query? You say: Open the query and then enter start and end dates. You would then have to re-run the query to get the information you need with the start and end date.

If you would like help figuring out why the query doesn't return the correct records, we'd have better luck helping you figure that out if you post the SQL of the query (switch view to SQLView instead of Query Design Grid).



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]

Here's the SQL

SELECT Bookings.Start_Date, Bookings.End_Date, Bookings.[Car Registration], [start Date] AS Expr1
FROM Bookings
WHERE ((([start Date]) Between [Start Date] And [End Date]));

I hope that's a little more illuminating

 

[SkipVought]

Hi,

Chance are your entered dates are TEXT and do NOT have the form of yyyy-mm-dd. Any other TEXT form will not collate correctly.

Best to Convert and text string to a REAL DATE via thenDateSerial function.

Skip,

_[red][/red]

 

[BSman]

If you use the form you can set the data type for each control as a date so you won't run into problems with a field being a text rather than a date.

I assume that the result of your query is that all records in the Bookings table are displayed, not just those in the date range. Try writing the query with actual dates in the criteria of the query so you can be sure that the result is correct. If that works, then you know that there is a problem with the way you are asking for the dates (although the separate criteria form would take care of that).

Bob

 

[Remou]

How about trying pasting this into the SQL view of a new query, you will need to have frmBookings open and the controls filled in.

[tt]SELECT Bookings.Start_Date, Bookings.End_Date, Bookings.[Car Registration]
FROM Bookings
WHERE Bookings.Start_Date >=Forms!frmBookings![Start Date] And Bookings.End_Date<=Forms!frmBookings![End Date];[/tt]

 

[PHV]

list of all the cars that are available in this date range
Not in the Bookings table for any day in this date range ?

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

 

[lespaul]

I don't think you can use the same parameter name as the field name....

SELECT Bookings.Start_Date, Bookings.End_Date, Bookings.[Car Registration], [b][start Date] AS Expr1[/b]
FROM Bookings
WHERE ((([b][start Date][/b]) Between [b][Start Date][/b] And [End Date]));

Do you have a field named [start Date] in the table? I can see that you have Bookings.Start_Date...do you have the same field in the table twice?



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

 

[Remou]

(lespaul, in case it is not available to you, the first image posted shows a control on the form added to the query grid as a field.)

 

[BSman]

Just realized something. Try this:

SELECT Bookings.Start_Date, Bookings.End_Date, Bookings.[Car Registration]
FROM Bookings
WHERE ((([start_Date]) Between [Start Date] And [End Date]));


The terms "Start Date" and "End Date" should be the prompts for the user to enter those dates. (Although I still recommend using a criteria form.)

Just as an aside, I have found over the years that it's better to avoid spaces and underlines in column/field names. By using upper and lower case (which you have) it's easy to read a column name without spaces, while avoiding the need for brackets around column names. However, the query you wrote could just as easily have said:

SELECT Bookings.Start_Date, Bookings.End_Date, Bookings.[Car Registration]
FROM Bookings
WHERE ((([start_Date]) Between [Enter start date==>] And [Enter ending date==>]));

This way the user would get prompted with "Enter start date==>", for example.

Bob

 

[lespaul]

Remou,
I looked at that image and the impression I get is the query would read:

WHERE Forms!FormBooking!StartDate BETWEEN tblBookings.Start_Date AND tblBookings.End_Date

How can the BETWEEN be the values in the table and the criteria be the Form information?

Les

 

[PHV]

How can the BETWEEN be the values in the table and the criteria be the Form information?
????
I don't see the problem, Leslie.

I've often suggested some criteria like this:
WHERE Forms!mainform!somevalue In ([field1],[field2],[field3])
to avoid unnecessary ORs

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

 

[BSman]

Les,

That's backwards. The value in the table should be between the values entered by the user in the two controls on the form.

As in:

WHERE tblBookings.Start_Date BETWEEN me.txtUserEnteredStartDate and me.txtUserEnteredEndDate

(me refers to the current form)

Bob

 

[berniecliffdon]

Thanks everyone.

The last SQL Bob put up as worked for me. Now Im going to try and piece it all together and try to appreciate how and why it works.

I'll try to take all your points about the inconsistant field names/formating.

Now I know my query works im going to try and improve it as you suggested Leslie, by having the user select more than one criteria for which car they would like to rent, in form format.

Skip, im a little bit unsure what you mean by REALDATE. I can tell you that in table design of my booking table the dates are both in Medium date format (19-June-94). If the dates If the Query are in REAL DATE does that mean I could use them in calculations? I ask because that's the only other query hurdle I have to overcome for this database.

Thanks All, this is all really helpful

 

[lespaul]

Les,

That's backwards. The value in the table should be between the values entered by the user in the two controls on the form.

I know it's backwards, that was what I was trying to explain.....look at the first image (that I was referred to by Remou) and that's what the information in the Query Design Grid will translate to in SQL.

PHV,
If the query says

WHERE SomeField Between SomeStartDateInTable and SomeEndDateInTable

wouldn't that automatically include EVERY date in the table?

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

 

[PHV]

Leslie, sorry to be so dense, but I really don't see your point.
The issue I saw in a previous post:
WHERE StartDate Between StartDate And EndDate
in this case, obviously, all rows meet such criteria.

But:
WHERE SomeDateConstant Between SomeStartDateInTable And SomeEndDateInTable
is a real filter.

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