between dates

[slickdock]

I need my query to return all records created between 1/1/09 and 2/1/09, but
my [DateCreated] field is actually a date/time field, showing, for example,
1/1/09 12:58PM. How can I strip out the time from the [DateCreated] field in
my query, so that I can satisfy my BETWEEN parameter?

I am populating the query parameter with an unbound form that has startdate
and enddate fields that are formatted as Short Date. My Query parameter on
[DateCreated] field is: Between [forms]![DateParameters]![StartDate] and
[forms]![DateParameters]![EndDate].

Thank you.

 

[Douglas J. Steele]

Easiest way would be to set your criteria to

Between [forms]![DateParameters]![StartDate] and DateAdd("s", 86399,
[forms]![DateParameters]![EndDate])

 

[slickdock]

Thank you, it works....but huh???

Easiest way would be to set your criteria to

Between [forms]![DateParameters]![StartDate] and DateAdd("s", 86399,
[forms]![DateParameters]![EndDate])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need my query to return all records created between 1/1/09 and 2/1/09,
but
my [DateCreated] field is actually a date/time field, showing, for
example,
1/1/09 12:58PM. How can I strip out the time from the [DateCreated] field
in
my query, so that I can satisfy my BETWEEN parameter?

I am populating the query parameter with an unbound form that has
startdate
and enddate fields that are formatted as Short Date. My Query parameter
on
[DateCreated] field is: Between [forms]![DateParameters]![StartDate] and
[forms]![DateParameters]![EndDate].

Thank you.

 

[John W. Vinson]

I need my query to return all records created between 1/1/09 and 2/1/09, but
my [DateCreated] field is actually a date/time field, showing, for example,
1/1/09 12:58PM. How can I strip out the time from the [DateCreated] field in
my query, so that I can satisfy my BETWEEN parameter?

I am populating the query parameter with an unbound form that has startdate
and enddate fields that are formatted as Short Date. My Query parameter on
[DateCreated] field is: Between [forms]![DateParameters]![StartDate] and
[forms]![DateParameters]![EndDate].

Thank you.

The problem is that #07/28/2009 20:09:54# is NOT in fact BETWEEN 07/01/09 AND
07/28/09 - the end date corresponds to midnight at the beginning of the date,
and the date/time value in the table is over 20 hours later than that, outside
the range.

Duane's solution checks for times up to the last second of the day; another
approach is to not use the BETWEEN operator but instead

= [forms]![DateParameters]![StartDate] and < DateAdd("d", 1, [forms]![DateParameters]![EndDate])

Note the less-than sign < in the second parameter - that will get all values
up to but not including midnight at the start of the next day.

 

[Douglas J. Steele]

Yes, except that involves a function call for every row in the table,
whereas my suggestion only involves a single function call. Usually doesn't
make an appreciable difference, but it could.

 

[Douglas J. Steele]

Duane's solution checks for times up to the last second of the day

Hey, just because Duane & I have been roommates at the last two summits
doesn't mean we're interchangeable! <g>

 

[Marshall Barton]

Seems it would be easier to use DateValue() . DateValue([InputDate]) returns
the short date with no time stamp.

Ahh, DateValue returns the date with a time part of
midnight. It has nothing to do with the format that will be
used to display the value.

Whether it's easier to write than Doug's suggestion depends
on the person creating the query and what else the query is
doing (It can make a Totals type query more difficult).

The most important factor is that Doug's expression allows
indexing on the date field to be used to speed up the query.

Using DateValue prevents indexing from being used so Access
will have to resort to reading every record in the table.
You may not notice it with a small table, but with a large
table, it can take a LOT longer to run.

 

[John W. Vinson]

Hey, just because Duane & I have been roommates at the last two summits
doesn't mean we're interchangeable! <g>

oops... sorry Douglas, fingers got ahead of my brain!

 

[Douglas J. Steele]

oops... sorry Douglas, fingers got ahead of my brain!

Just to clarify, your saying the difference between this and DateAdd() is
indexing and DateAdd() would run faster?

Between [forms]![DateParameters]![StartDate] and DateValue(
[forms]![DateParameters]![EndDate])

I think you may have misunderstood something.

The assumption is that the date field being checked includes date and time,
and that the EndDate text box being used in the criteria only includes date.
If EndDate only includes date, then it will never catch any rows where the
date field being checked occurred at some time on that date. It's only by
adding 86399 seconds (the number of seconds, less one, in a full day) that
you'll catch those dates. Using
DateValue([forms]![DateParameters]![EndDate]) will do nothing.

The suggestion made to use DateValue was to put a calculated field in the
query that stripped off the time using the DateValue function, and then
using Between [forms]![DateParameters]![StartDate] and
[forms]![DateParameters]![EndDate] would work. As I stated else thread,
doing that would involve one function call for each row in the table.
Putting a function call in the criteria, however, only requires a single
function call, regardless how many rows are in the table.

 

[Rick Brandt]

Just to clarify, your saying the difference between this and DateAdd()
is indexing and DateAdd() would run faster?

The idea is that you never want to apply criteria to an expression, but
rather directly to fields. Indexing is applied to fields and can only
used for criteria applied directly to those fields without modification.

Example 1:

SELECT *
FROM SomeTable
WHERE SomeNumberField = 1234

In the example above an index on the field SomeNumberField could be
utilized to make the query faster.

Example 2:

SELECT *
FROM SomeTable
WHERE (SomeNumberField + 0) = 1234

Now, since our expression just adds a zero to the field it has no effect
on the value, but in this case the index cannot be used and the entire
table has to be scanned. So the point is not so much that the expression
has to be evaluated, but that any expression regardless of how basic it
is forces a table scan.

If the criteria value on the right-hand side of the operator is based on
an expression that is not a problem because that will be evaluated only
once, not once per row. That is of course, unless that expression makes
a field reference as well. Consider these examples...

SELECT *
FROM SomeTable
WHERE SomeNumberField = SomeOtherNumberField

Here indexes on both fields can be used.

SELECT *
FROM SomeTable
WHERE SomeNumberField = (SomeOtherNumberField + 5)

Here an index can be used for the first field, but not the second.

SELECT *
FROM SomeTable
WHERE (SomeNumberField * 2) = (SomeOtherNumberField + 5)

Here no indexes can be used.