Code on Format

[Guest]

I would like to have the text within a date field turn red and a message box
appear if the date entered is greater 90 days from current date. Can this be
done?

 

[John W. Vinson]

I would like to have the text within a date field turn red and a message box
appear if the date entered is greater 90 days from current date. Can this be
done?

Pretty easily, in A2000 or later.

Select the textbox; on the menu choose Format... Conditional Formatting. Use
an Expression like

DateAdd("d", 90, Date()) < [datefield]

and set the format of the textbox appropriately. You can have a second textbox
and set its visiblity in the same way.

John W. Vinson [MVP]

 

[Guest]

Thanks John,

My version is 2003 and i have tried your code I have tried using your code
in the design view of the subform but it does not change color when i type in
a date like january 1 2007 which is way beyond the 90 day time frame. Also,
how would i get a message box to appear on enter if condition is true?

I would like to have the text within a date field turn red and a message box
appear if the date entered is greater 90 days from current date. Can this be
done?

Pretty easily, in A2000 or later.

Select the textbox; on the menu choose Format... Conditional Formatting. Use
an Expression like

DateAdd("d", 90, Date()) < [datefield]

and set the format of the textbox appropriately. You can have a second textbox
and set its visiblity in the same way.

John W. Vinson [MVP]

 

[John W. Vinson]

The code I posted - and I may have misineterpreted - will detect those records
where the value in Datefield is either in the past or in the next 90 days.
Records where Datefield is after 90 days from today will return "false".

I cannot see your form, nor do I know exactly what you used in your
expression.

To make a message box pop up and appear, you'll need to use the MsgBox()
function in some appropriate form event. I don't know what event that would be
since I don't know how your database is set up - perhaps the Current event
which fires when you move to a record. The code might be

Private Sub Form_Current()
If DateAdd("d", 90, Date()) < Me![Datefield] Then
MsgBox "The date is within the next 90 days or in the past", vbOKOnly
Else
MsgBox "The date is over 90 days in the future", vbOKOnly
End If
End Sub

Thanks John,

My version is 2003 and i have tried your code I have tried using your code
in the design view of the subform but it does not change color when i type in
a date like january 1 2007 which is way beyond the 90 day time frame. Also,
how would i get a message box to appear on enter if condition is true?

I would like to have the text within a date field turn red and a message box
appear if the date entered is greater 90 days from current date. Can this be
done?

Pretty easily, in A2000 or later.

Select the textbox; on the menu choose Format... Conditional Formatting. Use
an Expression like

DateAdd("d", 90, Date()) < [datefield]

and set the format of the textbox appropriately. You can have a second textbox
and set its visiblity in the same way.

John W. Vinson [MVP]

John W. Vinson [MVP]

 

[Guest]

Thanks for the response John. The field is for InvDate and all i need is
when user enters a date greater than 90 days from current date DATE() then a
message box appears as a reminder with a message like Invoice has exceeded 90
days. The user can still move on to other fields after the reminder but on
exit i wanted the date to turn red. Can this be done?

The code I posted - and I may have misineterpreted - will detect those records
where the value in Datefield is either in the past or in the next 90 days.
Records where Datefield is after 90 days from today will return "false".

I cannot see your form, nor do I know exactly what you used in your
expression.

To make a message box pop up and appear, you'll need to use the MsgBox()
function in some appropriate form event. I don't know what event that would be
since I don't know how your database is set up - perhaps the Current event
which fires when you move to a record. The code might be

Private Sub Form_Current()
If DateAdd("d", 90, Date()) < Me![Datefield] Then
MsgBox "The date is within the next 90 days or in the past", vbOKOnly
Else
MsgBox "The date is over 90 days in the future", vbOKOnly
End If
End Sub

Thanks John,

My version is 2003 and i have tried your code I have tried using your code
in the design view of the subform but it does not change color when i type in
a date like january 1 2007 which is way beyond the 90 day time frame. Also,
how would i get a message box to appear on enter if condition is true?

I would like to have the text within a date field turn red and a message box
appear if the date entered is greater 90 days from current date. Can this be
done?

Pretty easily, in A2000 or later.

Select the textbox; on the menu choose Format... Conditional Formatting. Use
an Expression like

DateAdd("d", 90, Date()) < [datefield]

and set the format of the textbox appropriately. You can have a second textbox
and set its visiblity in the same way.

John W. Vinson [MVP]

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for the response John. The field is for InvDate and all i need is
when user enters a date greater than 90 days from current date DATE()

That sentence is ambiguous, and I do not know what it means.

Do you mean a date more than 90 days IN THE PAST?
Do you mean a date more than 90 days IN THE FUTURE?
Do you mean a date that falls in either of these categories?

then a
message box appears as a reminder with a message like Invoice has exceeded 90
days. The user can still move on to other fields after the reminder but on
exit i wanted the date to turn red. Can this be done?

In that case use the AfterUpdate event of the textbox in which the user enters
the date, as well as Conditional Formatting: assuming the textbox is named
txtInvDate try

Private Sub txtInvDate_AfterUpdate()
If DateDiff("d", Me![txtInvDate], Date()) > 90 Then
MsgBox "Your Message Here"
End If
End Sub

Depending on the resolution of the ambiguity above, you may need to reverse
the [txtInvdate] and Date() arguments.


John W. Vinson [MVP]

 

[Guest]

Thanks John,

The combination of your code and conditional format was the trick.

Thanks for the response John. The field is for InvDate and all i need is
when user enters a date greater than 90 days from current date DATE()

That sentence is ambiguous, and I do not know what it means.

Do you mean a date more than 90 days IN THE PAST?
Do you mean a date more than 90 days IN THE FUTURE?
Do you mean a date that falls in either of these categories?

then a
message box appears as a reminder with a message like Invoice has exceeded 90
days. The user can still move on to other fields after the reminder but on
exit i wanted the date to turn red. Can this be done?

In that case use the AfterUpdate event of the textbox in which the user enters
the date, as well as Conditional Formatting: assuming the textbox is named
txtInvDate try

Private Sub txtInvDate_AfterUpdate()
If DateDiff("d", Me![txtInvDate], Date()) > 90 Then
MsgBox "Your Message Here"
End If
End Sub

Depending on the resolution of the ambiguity above, you may need to reverse
the [txtInvdate] and Date() arguments.


John W. Vinson [MVP]