query by date

[Arunoday]

How can i extract the data with in a range of dates. suppose i want to
retrive the data from 01/01/2007 to 01/12/2007. so what will the coding. bcoz
here the opening date and the ending date will be mentioned in the different
text box and what will be the execution command ?

 

[Jeanette Cunningham]

Arunoday,
if you are doing this in code on a form, try something like this

strCriteria = "[OpeningDate] Between #" & Format$(Me!.[OpeningDate], _
"mm/dd/yyyy") & "# And #" & Format$(Me![EndDate], "mm/dd/yyyy") & "#"

If you are doing this in a query in design view, try something like this in
the criteria row under the date field:

Between #01/01/2007# and #01/12/2007"


Jeanette Cunningham

 

[Jeanette Cunningham]

Oops!!
that last line should be:

Between #01/01/2007# and #01/12/2007#

That should be a # at the end of the line, not a "

Arunoday,
if you are doing this in code on a form, try something like this

strCriteria = "[OpeningDate] Between #" & Format$(Me!.[OpeningDate], _
"mm/dd/yyyy") & "# And #" & Format$(Me![EndDate], "mm/dd/yyyy") & "#"

If you are doing this in a query in design view, try something like this
in the criteria row under the date field:

Between #01/01/2007# and #01/12/2007"


Jeanette Cunningham

How can i extract the data with in a range of dates. suppose i want to
retrive the data from 01/01/2007 to 01/12/2007. so what will the coding.
bcoz
here the opening date and the ending date will be mentioned in the
different
text box and what will be the execution command ?

 

[Dale Fye]

To expand on Jeanettes explaination.

The first thing you need to do is determine what your date data looks like.
Depending on whether the program or database uses the Date( ) or Now( )
functions to place data in your date fields, you will either have data that
looks like:

1/14/2007
or
1/14/2007 7:30:29 AM

When you format it as General Date in the table design view.

If the data include the time stamp, then you will need to strip out the time
portion of the data using the DateValue( ) function. The reason for this is
that date/time values are stored as double precision numbers (7:33:32 AM, on
1/14/2008 is actually stored as 39461.315....). When you use the criteria
that Jeanette provided, what you are actually comparing the date portions of
this data, so if I want to filter all of the dates from 1 Jan through 14
January, I either need to use a where clause that looks like:

WHERE [DateField] between #1/1/2008# and #1/15/2008#
(this method will also return any records that fall at exactly midnight on
1/15/2008)

So you could use:

WHERE [DateField] >= #1/1/2008#
AND [DateField] < #1/15/2008#

Or, you can use:

WHERE DateValue([DateField]) between #1/1/2008# and #1/14/2008#

There is also a TimeValue function, in case you want to evaluate just the
time portion of a field that contains date/time data.

HTH
Dale