Date range query from a date/time format field?

Infiltrator64

Board Regular
Joined
Dec 19, 2004
Messages
63
I have a query that selects records that are in a date range a user enters when prompted.

The field this query looks at is a combination date/time.
Example format: 7/14/2006 10:15:00 AM

The problem I’m having is if I answer just 7/14/2006 to the date range prompt it doesn’t return any records. If I answer 7/13/2006 to 7/14/2006 it will return all records for 7/13/2006 but none for 7/14/2006.

The select criteria I use to query the date/time field is as follows;
>=[start] And <=[end]

I simply want to select all records that have records equal to or greater than [start] and less than or equal to [end].

Can someone help me with the correct select criteria to use to perform this? :unsure: :unsure:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Infiltrator64

Board Regular
Joined
Dec 19, 2004
Messages
63
This didn't help either. Can you give me an example? I tried a simple select criteria on this date/time field using #7/13/2006# and still didn't select any records.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
When you say 7/14/06 to 7/14/06 the query is only going to return records with a date and time of 7/14/06 00:00:00AM Not much of a chance of that happening. Because your records have the time included, you would have to say, as you have seen, from 7/13/06 to 7/14/06 to get all the records from Midnight (the start of 7/13/06) to Midnight of 7/14/06 (the start of 7/14/06). It is the time in the records that is throwing you the curve ball.
HTH
 

Infiltrator64

Board Regular
Joined
Dec 19, 2004
Messages
63

ADVERTISEMENT

Yes the time values are causing my issue.

I need help on how to perform the select criteria that will only look at the date value and ignore the time value.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
You can either add 1 to your EndDate so rather than looking for the records "Between 7/13/06 and 7/13/06" which we know does not work, you would be looking for the records "Between 7/13/06 and 7/14/06" which we do know works.

The other way would be to have a calculation to take the time out of the date field in your record. Here is that calculation:
Code:
NewDate: DateSerial(Year([DateFromRecord]), Month([DateFromRecord]), Day([DateFromRecord]))
 

Infiltrator64

Board Regular
Joined
Dec 19, 2004
Messages
63
i ended up using the following code in a calcualted column to deterine just the date.
Start Date:DateValue([start_date_time])

When I use the following select criteria in this new column i get the correct results, if I just answer 7/13/2006 i only get records for 7/13/2006 however the query is extreamly slow, I'm running the query aginst a remote server.
>=[start] And <=[end]

Can anyone sugguest a faster way to do this prompt for select criteria date on the new calculated date field?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
By getting just the date out of every date within your table, that will be quite slow. If you add 1 day to your "[end]" date, then (I believe), your query will go much faster. In fact, I just tested it on a table I have with 155,000 records in it. It was taking 3 seconds to get to the end of the record set while doing the DateValue statement, but only 1 second after I removed the DateValue, and then added 1 to the [end] date.
 

Forum statistics

Threads
1,137,330
Messages
5,680,857
Members
419,936
Latest member
rphill48

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top