‘Between’ operator (AXP (2002) SP-1)

Topic

New Reply

is there a way to force the “between” logical operator to evaluate inclusive? I am evaluating data for “last month”. When I write a date criteria like: “between #9/1/2002# and #9/30/2002#”, it only produces data literally “between” those dates, leaving out the 1st and last dates of the month (which I guess is what I should be expected).

Reply | Quote

Replies

That’s strange, because Between … And … is supposed to be inclusive. One thing to check: #9/30/2002# is interpreted as midnight at the beginning of September 30. If the date field in your table has been filled with date-and-time values, for instance by the function Now(), then records with a date 9/30/2002 but with a time after midnight will not meet the criteria. If the date field has been formatted to show only a date, you wouldn’t see the time part. Records with a date 9/1/2002 should still be included, however.

Reply | Quote

I believe that the field has been formatted with both date and time, which would account for no data on the last day of the month. I guess I have to look for “between 9/1/2002 and 10/1/2002” in order to get at the last day of the month.

Reply | Quote

I believe that the field has been formatted with both date and time, which would account for no data on the last day of the month. I guess I have to look for “between 9/1/2002 and 10/1/2002” in order to get at the last day of the month. From what you indicated, this would not include data from 10/1/2002, as long as the field was actually formated with date and time.

Thanks for your help!

Reply | Quote

Why don’t you format your date field as either Short, Medium, or Long date in the table? These formats do not save the time and your between expression will work fine.

Reply | Quote

You still need to be careful not to use Now() to initialize values or the time of day (in addition to the date) will still be stored even though it won’t be displayed.

Reply | Quote

Actually, it would include midnight of the penultimate date, so between may not be the best way if you actually have records that are timestamped for midnight. We use date/time fields a lot and don’t use between for that reason.

Reply | Quote