In Between Function?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Just wondering how you would say:

If PaymentDate is in between 1/1/2003 and 1/31/2003 Then
<Commands>
End If

I am using Access 2002

Thanks for any help you can give me!!

Mike
 
Michael said:
Just wondering how you would say:

If PaymentDate is in between 1/1/2003 and 1/31/2003 Then
<Commands>
End If
Click to expand...

If Me!PaymentDate BETWEEN #1/1/2003# AND #1/31/2003# Then...
 
Mike
Here is sample for you - hopefully you will not be using hard coded values
.....

Function test0927(InputDate As Date) As Integer
Const Startdate = "1/1/2003"
Const EndDate = "12/31/2003"
If (InputDate >= Startdate) And (InputDate <= EndDate) Then
test0927 = 1
Else
test0927 = 0
End If

End Function
 
If Me!PaymentDate BETWEEN #1/1/2003# AND #1/31/2003# Then...
Click to expand...

This creates real problems if there are any time values involved, because
#01/31/2003 12:45# is not within the range...

It's safer to use real comparisons:

If #2003-01-01# <= Me!PaymentDate and Me!PaymentDate < #2003-02-01#

because that goes up to the last microsecond before midnight. An
alternative would be to use the DateValue() function:

If #2003-01-01# <= DateValue(Me!PaymentDate) And _
DateValue(Me!PaymentDate) <= #2003-01-31#

BETWEEN is really only safe with integer values, and the DateTime type is a
high-precision float.

Hope that helps


Tim F
 
Back
Top