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


"laavista" <(E-Mail Removed)> wrote in message
news:0EB8F5ED-375A-4187-97A5-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
news:0EB8F5ED-375A-4187-97A5-(E-Mail Removed)...
>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?

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0EB8F5ED-375A-4187-97A5-(E-Mail Removed)...
> >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?
>
>
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0EB8F5ED-375A-4187-97A5-(E-Mail Removed)...
> >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?
>
>
>