conditional format using global variable

  • Thread starter Thread starter laavista
  • Start date Start date
L

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?
 
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
 
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()
 
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!



Graham Mandeno said:
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

laavista said:
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?
Click to expand...
Click to expand...
 
I just had to share... it worked!!!

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

Thanks again!!



Graham Mandeno said:
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

laavista said:
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?
Click to expand...
Click to expand...
 
Back
Top