Query not listing the 31st of the month

[schulzy175]

I have a query that runs just fine and it list all dates from beginning to the end of the month. The problem is that it won't list 8/31/17. I have it set:

>= #8/1/17# and <= #8/31/17#

but it wont list anything that is dated 8/31/17.

I had to change the code to:

>= #8/1/17# and <= #9/1/17#

for it to list 8/31/17.

[June7]

Is there a time component to the data in the field? If so, need to extract the date part so the time part is ignored which is actually rather tricky, otherwise have to use the #9/1/17# parameter.

Here is alternate structure that is inclusive of the two parameters, but again, would need to eliminate time part from the data.

BETWEEN #8/1/17# AND #8/31/17#

[schulzy175]

Yea the date is a date/time format. I created a separate query with the format function to separate the date from time and then used that query in the main query. Ill try a work around if the time stamp is what is causing the issue. Thank you for the help and input.

[Micron]

Yea the date is a date/time format.

Don't think that's what the question was. A field that contains only date values is date/time format. A field that contains date and time is also date/time format. This 9/1/17 is cut off at 9/1/17 00:00:00 when time is not provided. I have had to deal with this before - not that difficult actually. If the field contains time, one way to handle this is to use the DateAdd function and add the appropriate number of minutes (or seconds) to the date. Such as BETWEEN 09/15/2017 AND DateAdd("n",1440,#09/21/2017#). 1440 (minutes) may not be the value that works best for you, but in that example it would push the date to midnight of the next day.

[orange]

You could also use something such as

BETWEEN #8/1/17# AND #8/31/17 11:59:59 PM#

to get all date/times for any part of 8/1/2017 thru to midnight 8/31/17

[ssanfu]

Can also use the Fix() function.

? Fix(#8/31/17 10:50:39 PM#) returns 8/31/2017

If BOM = #8/1/17#
If EOM = #8/31/17 10:50:39 PM#
try

BETWEEN BOM AND Fix(EOM)

[orange]

Steve,

I tried using this set up

id	plannedEnd	plannedStart
2	01-Aug-2017 1:00:00 PM	01-Aug-2017 6:00:00 AM
3	29-Aug-2017 10:00:00 PM	27-Aug-2017 9:00:00 AM
4	31-Aug-2017 11:00:00 PM	31-Aug-2017 8:30:00 PM

Using this query

SELECT ForumDateIssue.id, ForumDateIssue.plannedEnd, ForumDateIssue.plannedStart
FROM ForumDateIssue
WHERE (((ForumDateIssue.plannedStart) Between #8/1/2017# And Fix(#8/31/2017 23:50:39#)));

And get this result (doesn't include the 31-Aug-2017 start date

id	plannedEnd	plannedStart
2	01-Aug-2017 1:00:00 PM	01-Aug-2017 6:00:00 AM
3	29-Aug-2017 10:00:00 PM	27-Aug-2017 9:00:00 AM

[JoeM]

I have always just used LESS THAN instead of LESS THAN OR EQUAL TO the first day of the following month, i.e.

>= #8/1/17# and < #9/1/17#

(note that there is no equal sign after the "<")

Seems pretty easy and straightforward to me, without having to do any conversions or jump through any hoops (and works whether or not there is a time component).

[orange]

I agree JoeM. That seems to be the simplest. I was trying to use the BETWEEN option mentioned by June and micron.

[Micron]

I have always just used LESS THAN instead of LESS THAN OR EQUAL TO the first day of the following month, i.e.

>= #8/1/17# and < #9/1/17#

(note that there is no equal sign after the "<")
Seems pretty easy and straightforward to me, without having to do any conversions or jump through any hoops (and works whether or not there is a time component).

I would agree as the designer and only user. Problem is, it's not intuitive to everyone else. They expect to get everything that is relevant on the end date, which includes 11:59:59 of that date. Just asking for trouble if you think you can educate everyone else otherwise, IMHO.

[June7]

Been a few times wish I'd known about Fix(). @orange, maybe you needed morning cup of coffee before building that query. Have to use Fix() on the field, not the parameter.

SELECT ForumDateIssue.id, ForumDateIssue.plannedEnd, ForumDateIssue.plannedStart
FROM ForumDateIssue
WHERE Fix(ForumDateIssue.plannedStart) Between #8/1/2017# And #8/31/2017#;

[orange]

Good point. Thanks.

Here's another

SELECT ForumDateIssue.id, ForumDateIssue.plannedEnd, [ForumDateIssue].[plannedStart]
FROM ForumDateIssue
WHERE datevalue(ForumDateIssue.plannedStart) Between #8/1/2017# And #8/31/2017# ;

Note:
For use with data containing Date and Time eg Now()
DateValue() only gives Date; TimeValue() only gives the Time portion

[June7]

DateValue() will error if field is Null, Fix() will just return Null.