Multiple criteria in Conditional Formatting -- use SWITCH function

[Guest]

I'm trying to conditionally format a field in a form and a report based on
the values in two different fields, Due Date and Transaction Status. If the
Due Date occurs in the past week ([Due Date]<=(NOW()-7)) AND the Transaction
Status = "Closed", then I want to format the field (Due Date) in a particular
way.

I'm used to using the AND function in Excel, which I think would work
excellently, but it is not available in Access (correct?). I'm looking at
the SWITCH function, and am not sure how I would use it in a Conditional
Formatting expression in an Access form or report.

If I were using Excel, I would enter IF(AND([due
date]<=(Now()-7),[Transaction Status]="Closed"),,)

Any ideas if SWITCH will work here, and if not, how to use multiple criteria
(in this case, two criteria) in conditional formatting?

TIA for any help offered,
Steve
(e-mail address removed)

 

[strive4peace]

Hi Steve

try this:

Select your Due Date control

In Conditional Formatting -->

Expression Is -->

(
[Due Date]BETWEEN
(Date()-7) AND Date()
)
AND
(
[Transaction Status] = "Closed"
)

I used line breaks to make it easier to read -- the
expression will be one one line...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Crystal,

Thank you for your quick reply! I plugged your expression into the
Conditional Formatting expression box, and it didn't do anything (but no
errors either ;-).

I tried the separate components, i.e.,

([due date] Between Date()-7 And Date())

separate from

[rental status]="closed"

and each separate expression worked fine. It just doesn't seem to like the
"AND" statement in there, stringing both expressions. And I typed them in on
one line.

Any suggestions for what I might be doing wrong?

TIA,
Steve

Hi Steve

try this:

Select your Due Date control

In Conditional Formatting -->

Expression Is -->

(
[Due Date]BETWEEN
(Date()-7) AND Date()
)
AND
(
[Transaction Status] = "Closed"
)

I used line breaks to make it easier to read -- the
expression will be one one line...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I'm trying to conditionally format a field in a form and a report based on
the values in two different fields, Due Date and Transaction Status. If the
Due Date occurs in the past week ([Due Date]<=(NOW()-7)) AND the Transaction
Status = "Closed", then I want to format the field (Due Date) in a particular
way.

I'm used to using the AND function in Excel, which I think would work
excellently, but it is not available in Access (correct?). I'm looking at
the SWITCH function, and am not sure how I would use it in a Conditional
Formatting expression in an Access form or report.

If I were using Excel, I would enter IF(AND([due
date]<=(Now()-7),[Transaction Status]="Closed"),,)

Any ideas if SWITCH will work here, and if not, how to use multiple criteria
(in this case, two criteria) in conditional formatting?

TIA for any help offered,
Steve
(e-mail address removed)

 

[strive4peace]

Hi Steve,

the AND will not give it a problem

when you combine things, use parenthesis

(condition1) AND (condition2)

-- make sure after you set the condition that

1. you specify a format
I often start by making text red just so I can quickly see
if it is working

2. you have data that meets the condition

In case you have fields with no data, you may want to wrap
references with NZ

make sure
rental status
and
due date

are the control names -- I HIGHLY recommend removing the
space in the name for the Name property of the control (even
though your ControlSouce refers to the actual field name
which apparently has spaces. In the future, you should
avoid using spaces in names. An underscore character
provides a bigger seperation anyway since the space
character is so narrow

(nz([rental_status],"")="closed")
AND
(nz([due_date]) Between (Date()-7) And Date())

put parentheses around
(Date()-7)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal,

Thank you for your quick reply! I plugged your expression into the
Conditional Formatting expression box, and it didn't do anything (but no
errors either ;-).

I tried the separate components, i.e.,

([due date] Between Date()-7 And Date())

separate from

[rental status]="closed"

and each separate expression worked fine. It just doesn't seem to like the
"AND" statement in there, stringing both expressions. And I typed them in on
one line.

Any suggestions for what I might be doing wrong?

TIA,
Steve


:

Hi Steve

try this:

Select your Due Date control

In Conditional Formatting -->

Expression Is -->

(
[Due Date]BETWEEN
(Date()-7) AND Date()
)
AND
(
[Transaction Status] = "Closed"
)

I used line breaks to make it easier to read -- the
expression will be one one line...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I'm trying to conditionally format a field in a form and a report based on
the values in two different fields, Due Date and Transaction Status. If the
Due Date occurs in the past week ([Due Date]<=(NOW()-7)) AND the Transaction
Status = "Closed", then I want to format the field (Due Date) in a particular
way.

I'm used to using the AND function in Excel, which I think would work
excellently, but it is not available in Access (correct?). I'm looking at
the SWITCH function, and am not sure how I would use it in a Conditional
Formatting expression in an Access form or report.

If I were using Excel, I would enter IF(AND([due
date]<=(Now()-7),[Transaction Status]="Closed"),,)

Any ideas if SWITCH will work here, and if not, how to use multiple criteria
(in this case, two criteria) in conditional formatting?

TIA for any help offered,
Steve
(e-mail address removed)

 

[Guest]

Crystal, the NZ function clenched it for me. Thank you so much! Have a
wonderful weekend.

Steve

Hi Steve,

the AND will not give it a problem

when you combine things, use parenthesis

(condition1) AND (condition2)

-- make sure after you set the condition that

1. you specify a format
I often start by making text red just so I can quickly see
if it is working

2. you have data that meets the condition

In case you have fields with no data, you may want to wrap
references with NZ

make sure
rental status
and
due date

are the control names -- I HIGHLY recommend removing the
space in the name for the Name property of the control (even
though your ControlSouce refers to the actual field name
which apparently has spaces. In the future, you should
avoid using spaces in names. An underscore character
provides a bigger seperation anyway since the space
character is so narrow

(nz([rental_status],"")="closed")
AND
(nz([due_date]) Between (Date()-7) And Date())

put parentheses around
(Date()-7)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal,

Thank you for your quick reply! I plugged your expression into the
Conditional Formatting expression box, and it didn't do anything (but no
errors either ;-).

I tried the separate components, i.e.,

([due date] Between Date()-7 And Date())

separate from

[rental status]="closed"

and each separate expression worked fine. It just doesn't seem to like the
"AND" statement in there, stringing both expressions. And I typed them in on
one line.

Any suggestions for what I might be doing wrong?

TIA,
Steve


:

Hi Steve

try this:

Select your Due Date control

In Conditional Formatting -->

Expression Is -->

(
[Due Date]BETWEEN
(Date()-7) AND Date()
)
AND
(
[Transaction Status] = "Closed"
)

I used line breaks to make it easier to read -- the
expression will be one one line...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Steve Vincent wrote:

I'm trying to conditionally format a field in a form and a report based on
the values in two different fields, Due Date and Transaction Status. If the
Due Date occurs in the past week ([Due Date]<=(NOW()-7)) AND the Transaction
Status = "Closed", then I want to format the field (Due Date) in a particular
way.

I'm used to using the AND function in Excel, which I think would work
excellently, but it is not available in Access (correct?). I'm looking at
the SWITCH function, and am not sure how I would use it in a Conditional
Formatting expression in an Access form or report.

If I were using Excel, I would enter IF(AND([due
date]<=(Now()-7),[Transaction Status]="Closed"),,)

Any ideas if SWITCH will work here, and if not, how to use multiple criteria
(in this case, two criteria) in conditional formatting?

TIA for any help offered,
Steve
(e-mail address removed)

 

[strive4peace]

you're welcome, Steve happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal, the NZ function clenched it for me. Thank you so much! Have a
wonderful weekend.

Steve



:

Hi Steve,

the AND will not give it a problem

when you combine things, use parenthesis

(condition1) AND (condition2)

-- make sure after you set the condition that

1. you specify a format
I often start by making text red just so I can quickly see
if it is working

2. you have data that meets the condition

In case you have fields with no data, you may want to wrap
references with NZ

make sure
rental status
and
due date

are the control names -- I HIGHLY recommend removing the
space in the name for the Name property of the control (even
though your ControlSouce refers to the actual field name
which apparently has spaces. In the future, you should
avoid using spaces in names. An underscore character
provides a bigger seperation anyway since the space
character is so narrow

(nz([rental_status],"")="closed")
AND
(nz([due_date]) Between (Date()-7) And Date())

put parentheses around
(Date()-7)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal,

Thank you for your quick reply! I plugged your expression into the
Conditional Formatting expression box, and it didn't do anything (but no
errors either ;-).

I tried the separate components, i.e.,

([due date] Between Date()-7 And Date())

separate from

[rental status]="closed"

and each separate expression worked fine. It just doesn't seem to like the
"AND" statement in there, stringing both expressions. And I typed them in on
one line.

Any suggestions for what I might be doing wrong?

TIA,
Steve


:



Hi Steve

try this:

Select your Due Date control

In Conditional Formatting -->

Expression Is -->

(
[Due Date]BETWEEN
(Date()-7) AND Date()
)
AND
(
[Transaction Status] = "Closed"
)

I used line breaks to make it easier to read -- the
expression will be one one line...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Steve Vincent wrote:


I'm trying to conditionally format a field in a form and a report based on
the values in two different fields, Due Date and Transaction Status. If the
Due Date occurs in the past week ([Due Date]<=(NOW()-7)) AND the Transaction
Status = "Closed", then I want to format the field (Due Date) in a particular
way.

I'm used to using the AND function in Excel, which I think would work
excellently, but it is not available in Access (correct?). I'm looking at
the SWITCH function, and am not sure how I would use it in a Conditional
Formatting expression in an Access form or report.

If I were using Excel, I would enter IF(AND([due
date]<=(Now()-7),[Transaction Status]="Closed"),,)

Any ideas if SWITCH will work here, and if not, how to use multiple criteria
(in this case, two criteria) in conditional formatting?

TIA for any help offered,
Steve
(e-mail address removed)