conditional format using global variable

[laavista]

I am using Access 2007 and want to use the "conditional formatting" for a
field on a form.

My field is called "CancelByDate". I want the field to turn pink when the
CancelByDate is within a certain timeframe, e.g., if CancelByDate is
<= 30 days. It works well when I hard-code the number of days:
expression is: [CancelByDate]<=Date()+30

The user, though, is going to be setting the number of days they wish for
the reminder, so I want to use a global variable instead.
expression is: [CancelByDate]<=Date()+ intReminders_NumberOfDays

Access changes the variable to a string and puts quotes around the variable,
e.g.,
expression is: [CancelByDate]<=Date()+"intReminders_NumberOfDays"
and it doesn't work.

Any suggestions?

 

[Jeff Boyce]

Instead of using the built-in "conditional formatting" feature/function,
you'll probably need to "roll your own".

You could add code to an AfterUpdate event procedure for that particular
field, and use the reference to your "global variable" in that code. You
might want to/need to use the DateAdd() function to help out.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi laavista

You will need to write a small function that returns the value:

Public Function Reminders_NumberOfDays() as Integer
Reminders_NumberOfDays = intReminders_NumberOfDays
End Function

Then use that function in your conditional format expression:

[CancelByDate]<=Date()+Reminders_NumberOfDays()

 

[laavista]

Thank you both for your responses.

I'm going to try writing a function. Have only written one other so this
will be "good" for me to try.

I REALLY appreciate your help!

Hi laavista

You will need to write a small function that returns the value:

Public Function Reminders_NumberOfDays() as Integer
Reminders_NumberOfDays = intReminders_NumberOfDays
End Function

Then use that function in your conditional format expression:

[CancelByDate]<=Date()+Reminders_NumberOfDays()

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using Access 2007 and want to use the "conditional formatting" for a
field on a form.

My field is called "CancelByDate". I want the field to turn pink when the
CancelByDate is within a certain timeframe, e.g., if CancelByDate is
<= 30 days. It works well when I hard-code the number of days:
expression is: [CancelByDate]<=Date()+30

The user, though, is going to be setting the number of days they wish for
the reminder, so I want to use a global variable instead.
expression is: [CancelByDate]<=Date()+ intReminders_NumberOfDays

Access changes the variable to a string and puts quotes around the
variable,
e.g.,
expression is: [CancelByDate]<=Date()+"intReminders_NumberOfDays"
and it doesn't work.

Any suggestions?

 

[laavista]

I just had to share... it worked!!!

Graham, you did all the work, and I just implemented. It works like a charm!

Thanks again!!

Hi laavista

You will need to write a small function that returns the value:

Public Function Reminders_NumberOfDays() as Integer
Reminders_NumberOfDays = intReminders_NumberOfDays
End Function

Then use that function in your conditional format expression:

[CancelByDate]<=Date()+Reminders_NumberOfDays()

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using Access 2007 and want to use the "conditional formatting" for a
field on a form.

My field is called "CancelByDate". I want the field to turn pink when the
CancelByDate is within a certain timeframe, e.g., if CancelByDate is
<= 30 days. It works well when I hard-code the number of days:
expression is: [CancelByDate]<=Date()+30

The user, though, is going to be setting the number of days they wish for
the reminder, so I want to use a global variable instead.
expression is: [CancelByDate]<=Date()+ intReminders_NumberOfDays

Access changes the variable to a string and puts quotes around the
variable,
e.g.,
expression is: [CancelByDate]<=Date()+"intReminders_NumberOfDays"
and it doesn't work.

Any suggestions?