VBA Analog Of SQL's "Between" For Dates?

[(PeteCresswell)]

I'm doing this... and it works:
------------------------------------------------------------
Public Function DateWithinRange(ByVal theDate As Variant,
ByVal theBeginDate As Variant,
ByVal theEndDate As Variant) As Boolean
debugStackPush mModuleName & ": DateWithinRange"
On Error GoTo DateWithinRange_err

' PURPOSE: To determine whether-or-not a date is within a given range of dates
' ACCEPTS: - The date we want to check
' - First date in the range
' - Last date in range
' RETURNS: True if the date is within the range, Else False

Dim X As Long
Dim Y As Long

If ((IsDate(theDate)) And (IsDate(theBeginDate)) And (IsDate(theEndDate))) Then
X = DateDiff("d", theBeginDate, theDate)
If X > -1 Then
Y = DateDiff("d", theDate, theEndDate)
If Y > -1 Then
DateWithinRange = True
End If
End If
End If

DateWithinRange_xit:
DebugStackPop
On Error Resume Next
Exit Function

DateWithinRange_err:
BugAlert True, ""
Resume DateWithinRange_xit
End
----------------------------------------------------------

But it seems like wretched excess for such a "bread-and-butter" calculation.

(Albeit some of the excess is for debuggability - as in storing the diffs
in the temp fields X and Y)

So... what VBA syntax am I missing that would do it more concisely?

 

[Todos Menos [MSFT]]

yeah the problem with that-- is that you'll be doing a calculation for
each row-- whether you were doing this in SQL queries

better to use between, because between can seek an index quite nicely

or do you mean.. you're really doing this in VBA

Dim dStart as Date
Dim dEnd as Date
Dim bThis as Date

dStart = #4/1/2007#
dEnd = #4/30/2007#
dThis = Date()

and now.. you're doing this:
Msgbox(DateWithinRange(dStart, dEnd, SThis)


?

why not just use 'IF' ??

if dThis > dStart and dThis < dEnd Then
Debug.print "Is between"
Else
Debug.print "Not Between"
End If

or better yet LoL

Select Case (dThis > dStart) = (dThis < dEnd)
Case True
Debug.print "IsBetween = True"
Case False
Debug.print "IsBetween = False"
End Select

 

[Douglas J. Steele]

If ((IsDate(theDate)) And (IsDate(theBeginDate)) And (IsDate(theEndDate)))
Then
DateWithinRange = (theDate >= theBeginDate) And _
(theDate <= theEndDate)
End If

 

[Guest]

eval( "#" & thedate & "# between #" & thebegindate & "# and #" & theendate &
"#")

or, depending on what you are doing:

eval( val(thedate) & " between " & val(tbd) & " and " & val(ted) )

not that I would actually do it that way.

(david)

 

[(PeteCresswell)]

Per Douglas J. Steele:

If ((IsDate(theDate)) And (IsDate(theBeginDate)) And (IsDate(theEndDate)))
Then
DateWithinRange = (theDate >= theBeginDate) And _
(theDate <= theEndDate)
End If

That crossed my mind, but it seems like I've gotten into trouble doing straight
numeric compares on dates.

Come to think of it, maybe one problem was "dates" that were captured as "Now()"
instead of Date().... where the seconds are different.

 

[Tony Toews [MVP]]

Text that A a r o n K e m p f wrote snipped.

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews [MVP]]

That crossed my mind, but it seems like I've gotten into trouble doing straight
numeric compares on dates.

Come to think of it, maybe one problem was "dates" that were captured as "Now()"
instead of Date().... where the seconds are different.

Yes, that would certainly do it. I'm sure you know this but for the lurkers a date
field is a double field where the seconds are to the right of the decimal point.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

Per Douglas J. Steele:



That crossed my mind, but it seems like I've gotten into trouble doing straight
numeric compares on dates.

Come to think of it, maybe one problem was "dates" that were captured as "Now()"
instead of Date().... where the seconds are different.

I drafted the following response before seeing Douglas' very similar
post. I decided not to send it because I didn't want to get into it
about the arithmetic comparison operators being used directly on the
date values. In an effort to allow for simplicity in constructing
queries, MS relied on the the underlying data type for dates being
essentially numerical and thus comparable using standard arithmetic
comparison operations. I don't think that Access can overload the
comparison operators (or +) to be sensitive to a non-numeric data type
being compared/added. My rational for a Null comparison is that if any
of the inputs are unknown, then the value of the comparison is unknown
also. You likely don't want any times along with any dates when using
BETWEEN since that can exclude the entire day at the end of the interval
if you're not careful since time values cause the Date/Time value to go
beyond the initial second of the day implied by a pure date value.

Time is essentially continuous, until you discretize it into seconds or
into a finite number of decimal places. Converting a time value into a
number where comparing the numbers is the same as comparing the time
values reminds me of a mathematics concept called a Partially Ordered
Set or poset.


Maybe:

Public Function DateWithinRange(ByVal theDate As Variant,
ByVal theBeginDate As Variant,
ByVal theEndDate As Variant) As Variant

If Not IsNull(theDate) And Not IsNull(theBeginDate) And Not
IsNull(theEndDate) Then
DateWithinRange = (theDate >= theBeginDate And theDate <= theEndDate)
Else
DateWithinRange = Null
End If
End Function

James A. Fortune
(e-mail address removed)

 

[Todos Menos [MSFT]]

uh don't use variants and don't allow nulls?

what the hell is wrong with you, ****ing retards