Conditional Formatting based on Date 1

[CaptainBob007]

Hi all -

I'm using Access 2000, and am working on a form for data entry and I'm trying to input some automated date comparison. I have it implemented, but it's not behaving as I think it should.

I have a text box, with the short date format, and I'm inputting a date into it. I have conditional formatting set up as so:

If Field Value Is : less than or equal to : Date()

Then make the background red. So basically, if the date is either today or in the past, make it red, otherwise keep it the default.

Using today (7/13/2006) as an example, it works fine. if I input 7/13/2006 or 7/12/2006 it turns red, and if I put in 7/14/2006 it doesn't, just as expected.

However, if I input the date 10/1/2006 (clearly in the future) it also turns red. 9/30/2006 does not trigger this behavior. I'm thinking because "9/30/2006" starts with a 9, and "10/1/2006" starts with a 1, it somehow thinks that it is a lesser value. So basically, I don't believe it is actually comparing the dates.

Any ideas on how I can get it to behave?

 

[ProgramError]

I'd try
if field value is greater than date()

or try using now() instead of date()




Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[randy700]

Conditional formatting appears to treat the date entries as strings. Create your own in code. Something like...

Private Sub [COLOR=red]YourDateField[/color]_AfterUpdate()
    If Me.[COLOR=red]YourDateField[/color] <= Date Then
        Me.[COLOR=red]YourDateField[/color].BackColor = vbRed
    Else
        Me.[COLOR=red]YourDateField[/color].BackColor = vbWhite
    End If
End Sub

Randy

 

[MajP]

Here is why it does not work.

This would work fine if your textbox is bounded but not if it is unbounded:
If Field Value Is : less than or equal to : Date()

Therefore since it does not work, I assume your textbox is unbounded. So in the conditional formatting change to the following

Expression is: Cdate([txtBoxDate]) <= date ()


If it is unbounded the text box may be formatted as a date, but it is really just a variant.
All values in controls are variants until saved to the underlying field. That is why the conditional formatting works differently in bounded and unbounded controls.

 

[CaptainBob007]

Thanks a bunch Maj - yes it was unbound, and that took care of it!