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
9 3230
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
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 32,564
Recognized Expert Moderator MVP
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 32,564
Recognized Expert Moderator MVP
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 32,564
Recognized Expert Moderator MVP
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];
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 32,564
Recognized Expert Moderator MVP
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 : - [Proposed End Date] >= [Start Date] (of existing data).
- [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.
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 32,564
Recognized Expert Moderator MVP
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Adrian Parker |
last post by:
I have a server app which is sent a date in the local format of the client
machine. I've no control over the client app at all, so have to code at the
server end to cope with any problems. The server is in the US, so any
dates need to be in US format before they will convert using strtotime()
Here is what i'm doing at the moment..
//...
|
by: carverk |
last post by:
Hello All
I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.
I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.
|
by: Russell |
last post by:
I'm having a fit with a query for a range of dates. The dates are
being returned from a view. The table/field that they are being
selected from stores them as varchar and that same field also stores
other fields from our dynamic forms. The field is called
'FormItemAnswer' and stores text, integer, date, float, etc. Anything
the user can...
|
by: Gabriel Mejía |
last post by:
Services or applications using ActiveX Data Objects (ADO) 2.0 or greater may
intermittently return empty recordsets on queries that should be returning
valid results. At the time the problem occurs, the same queries successfully
return the expected data when run from non-ADO sources, such as from ISQL in
Microsoft SQL Server. This problem...
|
by: Bernd Hohmann |
last post by:
Hi there,
we're currently convert an old ISAM application to Java and DB2.
Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".
Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.
| |
by: David Kuhn |
last post by:
I have a query with a date field criteria of:
Between And
When the query is run, I am asked for the Start date and then the End
Date. So far, so good.
The records returned are all those in between those dates and not
records that are on the start or the end date.
For example, I enter "09/01/03" for the start date and "09/30/03" for
|
by: Mike Dundee |
last post by:
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a...
|
by: Tim D |
last post by:
Hi,
I originally posted this as a reply to a rather old thread in
dotnet.framework.general and didn't get any response. I thought it might be
more relevant here; anyone got any ideas? My questions are below...
"David Good" wrote:
> We have a network running both Win2k and Win2k3 webservers and our web sites
> reside on a UNC network...
|
by: Del |
last post by:
I have a parameter query that requires the user to enter a Start Date:
and End Date: and pull data between that date range. I am currently
using the following parameter;
Select *
From mytable
Where between and
This query will always be run on a Monday and the start date will
always be the previous Sunday. The end date will be the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |