Query between dates not using >= correctly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 31 Jan 2008 19:02 GMT

I have a Date field for my records. The date field is formated as a date.

I have a separate table that has start and end dates so people can specify
what range the want to look up records in. The Month Start and Month End
fields are both formatted as dates as well. They are related back into the
other table via a monthID string.

In the query, both tables are present and in the date colum the criteria is
set to the following:

>=[Month Begin] And <=[Month End]

It does not seem to be lookup up dates that are = to the Month End date.
Presently the dates are set to 1/1/08 to 1/31/08. It's ignoring the last 5
recordsd created on 1/31/08. This formula worked great in access 2007 with
the old db which was in access 2000 format. However it doesn't seem to be
working now. Using

>=#[Month Begin]# And <=#[Month End]#

just returns invalid syntax.

Any ideas why it isn't working?

Reply to this Message

Michel Walsh - 31 Jan 2008 19:31 GMT

You use # only around a CONSTANT date_time, and preferably expressed in a US
format. You don't use # around a container holding a date, a container being
such as a field, or a parameter, including control in an open form (some
thing named but the date_time value is not the name itself, but what is hold
into that named 'storage').

A date should, preferable, be a full date value, complete with the day and
the year. If the year is missing, OLEAUT32 will supply the actual year, but
that is a feature you are better to not use, imho.

If you are using a time, in addition to a date, in the TABLE, and if you
don't specify any time, in your parameter, then it is consider 00:00:00;
That would EXCLUDE anything between 00:00:00 and 23:59:59 occurring the
last day of the month. Better to use:

>= monthBegin AND < nextMonthBegin

(Note the < instead of <= )

I also assume you use date_time values, in your table. If you use STRING
instead of a date_time value, then you get a string comparison. "AZ" comes
before "Z", alphabetically, and ALSO "10" is smaller than "9", as strings.
Sure, as number, or as date_time, 10 is larger than 9.

Hoping it may help,
Vanderghast, Access MVP

>I have a Date field for my records. The date field is formated as a date.
>
[quoted text clipped - 19 lines]
>
> Any ideas why it isn't working?

Reply to this Message

Marshall Barton - 31 Jan 2008 19:34 GMT

>I have a Date field for my records. The date field is formated as a date.
>
[quoted text clipped - 11 lines]
>Presently the dates are set to 1/1/08 to 1/31/08. It's ignoring the last 5
>recordsd created on 1/31/08.

It is important to understand that the Format of a field has
little to do with the value of the field. The important
thing is the Type (and Size) and the Value of the field.

from what you said, I suspect that the field's value was set
using the Now function. If so the value contains a time
part that makes it later than midnight on 1/31. If that's
your problem, then use:

>=[Month Begin] And <[Month End]+1

Signature

Marsh
MVP [MS Access]

Reply to this Message

Mike - 31 Jan 2008 20:34 GMT

That did it (the +1)...i came up with one, too using Between and DateADD
which did the same basic thing, simple +1 is much shorter and straight to
the point.

Odd thing is it worked ok in the 2000 file format under 2007. The date has
been set using now() but can be changed (and is from time to time).

Thanks for the heads up guys :)

>>I have a Date field for my records. The date field is formated as a date.
>>
[quoted text clipped - 23 lines]
>
> >=[Month Begin] And <[Month End]+1

Reply to this Message

Michel Walsh - 01 Feb 2008 16:12 GMT

Note that technically, it can thus include data with the time stamp of the
first of February, at 00:00:00 (or with no time, just the date of the First
of February) while looking for data occurring in January. Indeed, "between"
uses <= for the upper limit. Using Between can be shorter, but may not be
"to the point".

Vanderghast, Access MVP

> That did it (the +1)...i came up with one, too using Between and DateADD
> which did the same basic thing, simple +1 is much shorter and straight to
[quoted text clipped - 4 lines]
>
> Thanks for the heads up guys :)

Reply to this Message

Marshall Barton - 01 Feb 2008 18:48 GMT

>That did it (the +1)...i came up with one, too using Between and DateADD
>which did the same basic thing, simple +1 is much shorter and straight to
>the point.

I was being lazy. The formal/"correct" way is to use:

>=[Month Begin] And <DateAdd("d", 1, [Month End])

Note that, as Michel pointed out, the difference is that
Between is equivalent to >=X And <=Y, while we used
>=X And <Y+1

>Odd thing is it worked ok in the 2000 file format under 2007. The date has
>been set using now() but can be changed (and is from time to time).

The only way I can see that happening is if the earlier
version did not use Now or nobody noticed the problem.

>"Marshall Barton" wrote
>> >=[Month Begin] And <[Month End]+1

Signature

Marsh
MVP [MS Access]

Reply to this Message