Start/End Date Query Problems when end date isn't <=

[Millie18]

Hi, I’m trying to set up a query to find all dates on or between a start and end date.

Table name and field names I’ve used:
Tbl_bookings
Booking No
Boarding Arrival Date
Boarding Departure Date

So far I’ve been using the criteria:
>=[Enter the arrival date]And<=[Enter the departure date]
under the arrival date field.

The SQL for the query is as follows:

SELECT tbl_bookings.[Booking No], tbl_bookings.[Boarding Arrival Date], tbl_bookings.[Boarding Departure Date]
FROM tbl_bookings
WHERE (((tbl_bookings.[Boarding Arrival Date])>=[Enter Arrival Date] And (tbl_bookings.[Boarding Arrival Date])<=[Enter Departure Date]));

The problem that I’m having is when I enter a set of two dates and the departure date is greater than the departure date stored in the bookings table.

For example the test data I’m currently using is as follows:
Test Data

Booking No    Arrives      Departs
    2       01/01/2008   08/01/2008
    5       07/01/2008   12/01/2008
    6       07/01/2008   12/01/2008
    7       07/01/2008   12/01/2008
    9       09/02/2008   14/02/2008
   10       09/02/2008   14/02/2008

I enter the dates 01/01/2008 to 07/01/2008 in the parameter value boxes and bookings 2,5,6,7 are returned as expected.

However if I enter 06/01/2008 to13/01/2008 only bookings 5,6,7 are returned yet booking 2 should also have been

I have the same problem again entering values such as 10/01/2008 to 11/02/2008, bookings 9,10 are returned but not 5,6,7.

I quite new to using access and was wondering if someone could point me in the right direction as to how to develop this query so that it works for when the departure date entered is not less than or equal to dates that are stored in the system.

Many Thanks

[Scott Price]

Have a good look through this article in our How-tos section.

You are running into a very common problem with SQL and the Access Jet engine interpreting dates in a certain way.

If you notice the dates that you say are assessed correctly, they are all 'ambiguous'... i.e. 07/01/2008 can mean July 1, 2008 (in North America) or 7 January, 2008 (in Great Britain). What is happening is that the SQL engine always look for the North American format, and interprets in this format.

You will need to pass your date values through at least the CDate() function, and possibly the Format() function first in order to get this to evaluate correctly.

The syntax will be: CDate([YourDateField) If this doesn't work try: CDate(Format([YourDateField], "m/d/yyyy"))

Regards,
Scott

[Millie18]

Have a good look through this article in our How-tos section.

You are running into a very common problem with SQL and the Access Jet engine interpreting dates in a certain way.

If you notice the dates that you say are assessed correctly, they are all 'ambiguous'... i.e. 07/01/2008 can mean July 1, 2008 (in North America) or 7 January, 2008 (in Great Britain). What is happening is that the SQL engine always look for the North American format, and interprets in this format.

You will need to pass your date values through at least the CDate() function, and possibly the Format() function first in order to get this to evaluate correctly.

The syntax will be: CDate([YourDateField) If this doesn't work try: CDate(Format([YourDateField], "m/d/yyyy"))

Regards,
Scott

Hi, Thanks for your help,

I've had a look through the article you mentioned and understand about the dates being interpreted wrongly and needing to use the CDate function to correct this.

I've had a play around the syntax you suggested but can't seem to get it to work. I am still quite new to this so probably going about it the wrong way. Do i need to use the CDate function individually for both my start and end date or together? And do i need to be using this within the SQL view of the query or the criteria in the design view of the query builder?

These are probably really obvious questions but ive been trying with different ways and don't seem to be getting anywhere.

Any help is much appreciated!

[NeoPa]

Millie,

Post in what you're currently using and explain where and when it goes wrong.
We can look at it for you and help you to resolve your issues.

CDate() is not generally necessary but the actual circumstances need to be known before we can judge the best way to proceed.

We will probably need to know the field types of any that are used too.

[NeoPa]

I maybe should have looked more closely at your original post :/

Anyway, try the Between X And Y syntax in your SQL :

SELECT [Booking No],
       [Boarding Arrival Date],
       [Boarding Departure Date]
FROM tbl_bookings
WHERE [Boarding Arrival Date] Between
      [Enter Arrival Date] And
      [Enter Departure Date];

[NeoPa]

Looking yet again, and this may seem like a dumb question, but aren't you really after a selection where the record spans a single date entered?

That would be more like :

SELECT [Booking No],
       [Boarding Arrival Date],
       [Boarding Departure Date]
FROM tbl_bookings
WHERE [Enter Date] Between
      [Boarding Arrival Date] And
      [Boarding Departure Date];

[Millie18]

Hi Thanks for your help!
I've tried using a Between statement before and tried it again as you suggested but it still isn't doing what i want it to do. I also played around with your 2nd suggestion.

What i want is for the user to be able to enter a proposed arrival date and departure date for a booking she is about to make. I then want the query to return all bookings already stored in the system, for and between the two dates she enters.

The problem i seem to be having though is when the user would enter for example: 06/01/2008 to 13/01/2008
the query brings back bookings 5,6,7 from my test data (as seen in my first post). But not booking no 2. However booking 2 doesn't depart untill 08/01/2008 so would be there for two of days, in the period of dates entered by the user. So would expect booking 2 to be returned.

Thanks again for your help, i beginning to whether there is actually no way around this.

[NeoPa]

Actually, this question is coming up so frequently recently I may have to write an article on it.

It's certainly possible, and involves checking that both the following are true :

1. [Proposed End Date] >= [Start Date] (of existing data).
2. [Proposed Start Date] <= [End Date] (of existing data).

Go away and play with that for a while, and if you can't manage to get it to work then come back and we can fill in the details for you.

[Millie18]

Hi again,

It's working!!! Thanks so much for all your help, i've been trying to sort this out for ages but wasn't getting anywhere. It's now working for the examples i'd been using before and i've tested it with other sets of dates and it works just how i wanted it to!

Thanks again,
Millie

[NeoPa]

Very pleased to hear it Millie.

Not so sure about all the surprise though - "Oh ye of little faith" :D

PS. You know I'm just kidding around right :)