Date Range Query

[Guest]

I have a date input field. I would like to run a query that allows me to
choose a date range and have it return all records whose date input field
fall into that range. i.e. from 4/2/2007 through 4/6/2007.

I can't seem to figure out the expression...

Thanks.

 

[Rick Brandt]

I have a date input field. I would like to run a query that allows
me to choose a date range and have it return all records whose date
input field fall into that range. i.e. from 4/2/2007 through
4/6/2007.

I can't seem to figure out the expression...

Thanks.

SELECT *
FROM TableName
WHERE [date input] >= [Enter Start Date]
AND [date input] < DateAdd("d", 1, [Enter End Date])

 

[Tom Lake]

I have a date input field. I would like to run a query that allows
me to choose a date range and have it return all records whose date
input field fall into that range. i.e. from 4/2/2007 through
4/6/2007.

I can't seem to figure out the expression...

Thanks.

SELECT *
FROM TableName
WHERE [date input] >= [Enter Start Date]
AND [date input] < DateAdd("d", 1, [Enter End Date])

You don't need # around the dates? Would BETWEEN work?
I've been using Access since 1.0 but I've only done things one way
in the past so thank you for showing me alternative solutions!

Tom Lake

 

[Guest]

You didn't indicate whether the "date input field" is populated with strictly
a date value, or whether it also contains time information (as with the NOW(
) function).

If the field only contains date information, you should be able to write a
query that looks something like:

SELECT *
FROM yourTable
WHERE [DateField] Between #4/2/2007# and #4/6/2007#

If the field contains time data, you will need to modify the query a little
because the above query would not capture date field entries that fall at
noon on April 6th. To get those values, you would need to either convert the
[DateField] to a date using the DateValue( ) function:

WHERE DateValue([DateField]) BETWEEN .....

or you could change the the WHERE clause to:

WHERE [DateField] >= #4/2/2007# and [DateField] < #4/7/2007#

If you are doing this via a parameter query, the first example would look
something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT *
FROM yourTable
WHERE [DateField] Between [StartDate] and [EndDate]

HTH
Dale

 

[Rick Brandt]

I have a date input field. I would like to run a query that allows
me to choose a date range and have it return all records whose date
input field fall into that range. i.e. from 4/2/2007 through
4/6/2007.

I can't seem to figure out the expression...

Thanks.

SELECT *
FROM TableName
WHERE [date input] >= [Enter Start Date]
AND [date input] < DateAdd("d", 1, [Enter End Date])

You don't need # around the dates? Would BETWEEN work?
I've been using Access since 1.0 but I've only done things one way
in the past so thank you for showing me alternative solutions!

Tom Lake

If believe if you set up those parameters in the parameter box of the query
and specify them as DateTimes then you shouldn't need the # delimiters. I
always assume DateTimes will (or might) contain non-midnight times so I
never try to use BETWEEN.