Solved Between operator

[MajP]

@Minty. I disagree with the above. I date field can definitely have a zero time component (fraction of a day since midnight). I did not say null. A zero time component is 12:00:00 AM. This is the default when you use a date picker or type in a date without the time. Also the Between operator is inclusive meaning it is a >= and <=. So if this is my data
tblTest tblTest

ID	dtmDate
1​	1/1/2021 6:00:00 PM​
2​	1/2/2021​
3​	1/3/2021​
4​	1/3/2021 3:13:00 PM​
5​	1/4/2021​

And I want values between 1/1 and 1/3, I cannot add an extra day to the end.

SELECT tblTest.dtmDate
FROM tblTest
WHERE (((tblTest.dtmDate) Between #1/1/2021# And #1/4/2021#));

That returns the below values including 1/4/2021

Query1 Query1

dtmDate
1/1/2021 6:00:00 PM​
1/2/2021​
1/3/2021​
1/3/2021 3:13:00 PM​
1/4/2021​

So I either need to first convert dtmDate only to date values or I need to change my between to include the end time

SELECT tblTest.dtmDate
FROM tblTest
WHERE (((tblTest.dtmDate) Between #1/1/2021# And #1/3/2021 23:59:59#));

 

[The_Doc_Man]

As there is now quibbling over one second, I will add a clarification.

You can use Between #1/1/2021# And #1/3/2021 23:59:59# to include all date/time combos on the first three days of 2021 as shown. But if there will never be an actual time entry at midnight anyway, you could use Between #1/1/2021# And #1/4/2021# - because the 2nd case stops at exactly midnight of 1/4/2021 when used in that context. If there is no actual data entry at midnight, the difference between the two cases (which is one second) becomes a matter of pedantry rather than technology.

 

[MajP]

But if there will never be an actual time entry at midnight anyway

NO, tha is wrong. It is totally the opposite of what you said. 90% of the time time when you enter a date you enter it without explicit time. So the time component is 0 or 12:00:00 AM.

This is a simple test

Public Sub testit()
  Debug.Print Format(Date, "mm/dd/yyyy hh:nn:ss am/pm")
End Sub

output: 05/19/2021 12:00:00 am

So depending on how data is actually entered or imported into the field there is very, very likely a date is entered without the time component. My example above proves exactly this and that is why add a bogus day is a bad idea. So no this is not arguing over a second.

 

[The_Doc_Man]

You misread my hypothetical situation. I was saying that IF no transactions were entered at exactly midnight (probably because nobody was working at that time of day), the argument was about a one-second difference between the two times I named. DO YOU DENY that the difference between #3-Jan-2021 23:59:59# and #4-Jan-2021# is one second when they are taking as in-line VBA literal dates?

If it happens that people CAN enter data at exactly midnight, then my IF statement fails on data entry conditions - but not on logic.

 

[Minty]

@MajP If you are using the between operator what you say is correct.
I was using the < comparison only not <= .

There is another gotcha with between.
Put the dates in backwards e.g.

Between LaterDate and EarlierDate

Now, for a beginner, in a purely logical function, I would argue that that should work. But it doesn't.
As you correctly stated it is evaluated as

>= LaterDate And <= EarlierDate

 

[zelarra821]

Hi guys.

What a debate has been generated. As it is not my mother tongue, it is a bit difficult for me to get the idea of what you are discussing.

However, in the end I have opted for @MajP's solution, which is to add + 0.9999.

Thank you very much for the help.

 

[Pat Hartman]

BETWEEN returns BOTH end points. If you don't want one of them, don't use BEWEEN use
WHERE someDate >= startDate AND someDate < endDate


OR if your problem is dates with times, then use the following

WHERE DateValue(yourdate) BETWEEN Forms!yourform!startdate AND < Forms!yourform!enddate +1