Proper >= And < Where Condition in VBA (1 Viewer)

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Hello folks,


Perhaps I've been starring at this too much. What I am trying to do with the following WHERE condition is replace BETWEEN with >= And < operands for my date range. I've played around with the placement of them to no avail. Any help is appreciated. Thank you much.



Code:
DoCmd.OpenReport ReportName, View:=acViewPreview, _
    WhereCondition:="VisitDate Between # " & Format(Me.FROMDTTbx, "mm\/dd\/yyyy") & _
   "# And #" & Format(Me.TODTTbx, "mm\/dd\/yyyy") & "#"
:banghead:
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you for the prompt reply Galaxiom.


It is my understanding that If i don't specify a time, Access assumes midnight and will not search anything pass midnight unless properly queried. The BETWEEN function does not accomplish this.

So if my users run a search from 6/1 - 6/30, if there is a record entered on 6/30 at 2:30 PM, it'll get ignored. Is this not correct?
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,833
Thank you for the prompt reply Galaxiom.


It is my understanding that If i don't specify a time, Access assumes midnight and will not search anything pass midnight unless properly queried. The BETWEEN function does not accomplish this.

So if my users run a search from 6/1 - 6/30, if there is a record entered on 6/30 at 2:30 PM, it'll get ignored. Is this not correct?
Exactly the same behaviour with either expression.

I generally add a day to the second date so that it picks times up to midnight at the end of the queried day. If all dates in your records have time only, records right on midnight would be included when perhaps they shouldn't. Which day midnight really belongs to is arbitrary anyway.

If you need to be precise, add a day and subtract a second.
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you Galaxiom for the explanation - much appreciated. For fun and giggles, could you show me where those operands need to be placed? Thanks again. :)
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,682
To avoid the issue, you could specify a spot typecast so that your "2:30" problem goes away.

To understand what I mean, remember that date/time fields and VBA Date variables are actually special interpretations of fields with data type DOUBLE (a floating-point number). The date reference is 1-Jan-1900 and the date is just the DOUBLE number of days and fractions of a day since midnight of the reference date. If you do the math, that means that in 2018 we are less than 44,000 days past that particular date.

Therefore, if you convert a date to a long integer (which has a capacity of over 2 billion), it will fit. SO if you want to avoid getting tripped up by fractions, do your comparisons of CLNG(date). Doing it that way, ...

Code:
IF ( CLNG( ActualDate ) >= CLNG( EarliestOKDate ) ) AND ( ( CLNG( ActualDate ) <= CLNG( LatestOKDate ) ) THEN GOTO DateIsOK
This way, you retain the fractional days if you need them later for reporting, but in the comparison you drop the fractions to see if something is within the acceptable range.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,833
Therefore, if you convert a date to a long integer (which has a capacity of over 2 billion), it will fit. SO if you want to avoid getting tripped up by fractions, do your comparisons of CLNG(date). Doing it that way, ...

Code:
IF ( CLNG( ActualDate ) >= CLNG( EarliestOKDate ) ) AND ( ( CLNG( ActualDate ) <= CLNG( LatestOKDate ) ) THEN GOTO DateIsOK
.
It does mean that a function needs to be applied to every record which will slow down the query. Admittedly it would probably be a very fast function but it would surely be better done using a date range so the index is all that is required to select.

BTW Situations that require a function be applied to datetime records (such as grouping by the date component) should have a Where condition applied to filter the records so that the conversion isn't needlessly applied to records outside the range of interest.

Inexperienced programmers will often use a Convert, Group, Having sequence because that is what is obvious in the query designer. A Where, Convert, Group solution performs much better. It is important to understand this difference.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,833
suggest simpler to add a day and use <
Then the request on original post has a point.:)

Code:
"VisitDate >= " & Format(Me.FROMDTTbx, "\#mm\/dd\/yyyy\#") & _
   " And VisitDate <" & Format(Me.TODTTbx + 1, "\#mm\/dd\/yyyy\#")

Notice how I have included the # inside the Format strings. It is less cluttered than concatenating them.

[code]Format(Me.TODTTbx + 1, "\#mm\/dd\/yyyy\#")
This section of the expression could instead be:
Code:
Format(DateAdd("d", 1, Me.TODTTbx), "\#mm\/dd\/yyyy\#")
Using arithmetic on the numbers that underlay the Date (as Doc mentioned above) is OK but one must be careful when connecting to other back ends via ODBC. The safest way to deal with dates is using the date functions provided by Microsoft.

Having said this, the only example I have concerns datetime values between SQL Server and Access on a time component where arithmetical comparisons greater than and less than return the wrong result.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,682
Pre-constrain the dates when building the Where condition for the report filter.

Code:
Dim FirstDate as Date
Dim LastDate as Date
Dim WhereCond as String
...
   FirstDate = CDate( FROMDTTbx )
   LastDate = CDate( TODTTbx ) 
   WhereCond = "( VisitDate >= #" & FormatDateTime( FirstDate, vbShortDate ) & " 00:00:00# )  AND ( VisitDate <= #" & FormatDateTime( LastDate, vbShortDate ) & " 23:59:59# )"
...
   DoCmd.OpenReport ReportName, View:=acViewPreview, _
    WhereCondition:=WhereCond
The SHORTDATE option on FormatDateTime forces the date to something like mm/dd/yyyy REGARDLESS of whether a time was part of the input. There will be no time specifier. So for the FROM you specify midnight and for the TO you specify one second before midnight of the next day. This is how I would implement Galaxiom's suggestion of "add a day and subtract a second."

His suggestion would also work, by changing from "<=" to "<" as the "TO" comparison operator. ONE of these two methods should do the trick.

And to be honest, if this is a very large table, G's comment about speed of the CLNG function is appropriate, too. But if we are talking about a few hundred or even a few thousand records, you would hardly notice the difference. If we are talking tens of thousands of records, it probably WOULD begin to be less efficient.
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
The_Doc_Man, Galaxiom, and, CJ_London -

Thank you all so much for your very thorough explanations/solutions. Much appreciate this forum.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom