change properties based on a date range

[kev747]

Hi all,

I'm having trouble setting the properties of a field, based on the value of the field itself.
I have a field on my form called "bywhen", which is a date field. Based on the value of this field, I want the properties to change to reflect the value. For example, if the field is populated with a value between todays date + 3, and todays date + 5, then I want the backcolor of the field to change to yellow.
Here is the code I've been trying to get to work -

**** start code *****

If Me.bywhen Is between(Now() + 3 And Now() + 5) Then
Me.bywhen.BackColor = 65535
Else: Me.bywhen.BackColor = 16777215
End If

**** end code ****

The error message keeps highlighting the "between".

What am I doing wrong? Obviously the code I'm using is incorrect, but I can't figure out what I should be using.

Any help greatly appreciated.

Kev.

 

[JunglesMcBeef]

G'day,

Maybe substitute the between line for this:

If (Me.bywhen >= (Now() + 3)) And (Me.bywhen <= (Now() + 5)) Then

 

[ordendelfai]

I tested this and it seemed to work ok. I checked Between and I believe that can only be used in SQL or Recordsets, not in strict VBA code (there is no library reference to it).

Sub test()
Dim Bywhen As Date

Bywhen = &quot;10/31/2003&quot;

If Bywhen >= (Now() + 3) And Bywhen < (Now() + 5) Then
MsgBox &quot;Works&quot;
Else
MsgBox &quot;doesn't work&quot;
End If

End Sub

 

[ordendelfai]

oops, left off the <= on the right side

 

[JunglesMcBeef]

Yeah, I stuffed up, just watch the brackets