Date Problem

[saintsman]

I've tried lots of different variations but still cannot get it to work. I need to highlight a field containing a date with a colour if it shows that the date has passed. (ie highlight if it is overdue).
I have tried variations of < "Date()" but can only get it to work if I put an actual date in, which is obviously only good for today.
Where am I going wrong?

Thanks in anticipation.

 

[SteveA]

Take away the quotes from your check.
ie: If Me![DueDate] < Date() Then

HTH
SteveA

 

[saintsman]

Simple eh?

Thanks very much

 

[SteveA]

No problems

SteveA

 

[Maestro]

Just to bring this one back from the dead, I've been trying to do something similar with a membership database I'm developing. For the On Open event I have:

If Me![expdate] < Date() Then Me![expdate].BackColor = "255"

Only problem is that this seems to be wrong because Access automatically removes the brackets after the word 'Date' and the code does not do anything.

Taking it a step further, anyone know how I could then alter that code so that 3 weeks after the expiry date is reached, the record is deleted altogether?

 

[Rich]

The brackets after Date in vba are not required, remove the quotes from around 255 and use the dot operator not the bang.
If Me.expdate < Date Then Me.expdate.BackColor = 255
Else Me.expdate.BackColor = 0
End If

If expdate is the name of your field then rename the control to something else ie.txtExpdate, you'll have to change the reference in the above examp. to suit.

 

[Maestro]

Excellent - thanks for that! There's still something not right though. When I now open the form the code comes up with:

"Compile Error: Else without If"

I now have the following, where access has added the ":" after the 'else' in line 2. Any ideas?

Private Sub Form_Open(Cancel As Integer)

If Me.txtExpdate < Date Then Me.txtExpdate.BackColor = 255
Else: Me.txtExpdate.BackColor = 0
End If

End Sub

 

[R. Hicks]

Try this:

If Me.expdate < Date Then
Me.expdate.BackColor = vbRed
Else Me.expdate.BackColor = vbWhite
End If

I don't think you want the BackColor to be 0 (Black)
(I think Rich was thinking of ForeColor)

HTH
RDH

[This message has been edited by R. Hicks (edited 11-04-2001).]

 

[Rich]

 

[Maestro]

OK problem sorted - thanks for your responses! I think the main reason for the code not doing anything was that being in te On Open event it ran only for the first record, so it's now in On Current.

I can also answer the second part of my question, and it goes something like:

If DateAdd("d", 21, [txtExpdate]) < Date Then
Me.Txtbox1 = Null
Me.Txtbox2 = Null
etc..

This is also an 'On Current' event at present, but that's no good because really I want it to run for all the records when the form is opened. But how?

I've just set up my database at work to test on machines other than he one I've been developing it on, but here I'm getting "Compile Error: Can't find project or library" with the 'Date' in the code highlighted. Changing this to 'Now' seems to have solved that ne though. I wonder if this is something to do with the new environment being part of a network?