Format based on value

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hi,

I have a form with three fields; Date, Day and Spend.

Every date in 2010 is listed and I want to disable or format in a different
colour the Spend field where the value of Day = "Saturday" or "Sunday".

I have done some research but cant think how to achieve this. Can anyone
help?

Thanks in advance

Martin
 
Hi Martin,
first of all I would avoid naming fields with reserved words as Date or Day
so I would suggest to change those names.
Then on the afterupdate of the field date(lets call it yourdate) you can
append this code

if weekday(yourdate)=vbsunday or weekday(yourdate)=vbsaturday then
spend.enabled=false
else
spend.enabled=true
endif

In this way enable or disable the field spend. If you prefer changing the
fore color set the property forecolor with the color you prefer or if you
wanna change the back color use the backcolor property.

HTH Paolo
 
Hi,

Thanks for the advice, I have changed my fields.

This code works but only when the very first row is a Saturday or Sunday.
What I need is it for to work for all days on the form. I show one month at
a time so there are at least 30 records on the form.

Martin
 
Well, if your data are on a continous form you can use the conditional
formatting on your field.
Select your field, then from the format menu choose conditional formatting,
then in condition 1 choose "expression is" and in the box on the right paste
this
Weekday([yourdate])=1 Or Weekday([yourdate])=7
and then choose the color for the field if the condition is met.

Cheers Paolo
 
Minor refinement. Because Sat and Sum are contiguous days,
the expression can be made slightly more efficient and a
little shorter:
Weekday([yourdate], 2) >= 6
--
Marsh
MVP [MS Access]

Well, if your data are on a continous form you can use the conditional
formatting on your field.
Select your field, then from the format menu choose conditional formatting,
then in condition 1 choose "expression is" and in the box on the right paste
this
Weekday([yourdate])=1 Or Weekday([yourdate])=7
and then choose the color for the field if the condition is met.
Click to expand...
 
Or, still using conditional formatting, you could use:

Weekday([yourDate], vbMonday) > 5
 
Thank you all for your help, got it working fine.

Martin

Paolo said:
Well, if your data are on a continous form you can use the conditional
formatting on your field.
Select your field, then from the format menu choose conditional formatting,
then in condition 1 choose "expression is" and in the box on the right paste
this
Weekday([yourdate])=1 Or Weekday([yourdate])=7
and then choose the color for the field if the condition is met.

Cheers Paolo

Martin said:
Hi,

Thanks for the advice, I have changed my fields.

This code works but only when the very first row is a Saturday or Sunday.
What I need is it for to work for all days on the form. I show one month at
a time so there are at least 30 records on the form.

Martin
Click to expand...
Click to expand...
 
Back
Top