Date field

[kev34]

Hi
I have a database that includes a report where the date field changes to red and italic if its overdue. The code is:

If Me![TestDate] <= Now Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbRed
Else
Me![TestDate].FontItalic = False
Me![TestDate].FontBold = False
Me![TestDate].ForeColor = vbBlack
End If

I need to now change it to include the above but change the field to orange if the item is due in the next 30 days.

So to recap, any items overdue are in red, any items due in the next 30 days are in orange and all other items are in black.

Can anyone help me with this please?

Kev

 

[Isskint]

You need to add an extra conditional test in your IF statement using the DateDiff function.

If Me![TestDate] <= Now Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbRed
ElseIF DateDiff(your conditions here) then
...
...
else
Me![TestDate].FontItalic = False
Me![TestDate].FontBold = False
Me![TestDate].ForeColor = vbBlack
End If

There is no vbOrange so you will need to set the numeric value yourself

 

[kev34]

Hi
many thanks for the quick reply.
No problem with orange - I can use vbYellow.

However could I ask for a bit more help with the condition?
I was thinking something along the lines of:

If Me![TestDate] <= Now Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbRed
ElseIF DateDiff [TestDate] <=30 Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbYellow
Else
Me![TestDate].FontItalic = False
Me![TestDate].FontBold = False
Me![TestDate].ForeColor = vbBlack
End If

But this doesnt work - could I trouble you to point me in the right direction?

Kev

 

[Isskint]

DateDiff is a function which has a set of parameters. DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) only interval, date1 and date2 are required. In your code type DATEDIFF( and intellisense will prompt you for the parameters it requires.

replace ElseIF DateDiff [TestDate] <=30 Then

WITH

ElseIf Datediff("d",Date(),me![TestDate]) <=30 Then

 

[kev34]

Hi

thanks again with the prompt reply.

I have now got the following:

If Me![TestDate] <= Now Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbRed
ElseIf
DateDiff("d",Date(),Me![TestDate]) <=30 Then
Me![TestDate].FontItalic = True
Me![TestDate].FontBold = True
Me![TestDate].ForeColor = vbYellow
Else
Me![TestDate].FontItalic = False
Me![TestDate].FontBold = False
Me![TestDate].ForeColor = vbBlack
End If

However when I run the report I get a syntax error and if I click OK on the compile error window the ElseIf is highlighted...

Am assuming I am still missing something and would appreciate any help you can give me.

Many thanks

Kev

 

[Isskint]

ElseIf
DateDiff("d",Date(),Me![TestDate]) <=30 Then

Should be on 1 line

ElseIf DateDiff("d",Date(),Me![TestDate]) <=30 Then

 

[kev34]

Hi

Perfect!

Many thanks for all your help

Kev