If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple criteria in Conditional Formatting -- use SWITCH function
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 |
#2
|
|||
|
|||
Multiple criteria in Conditional Formatting -- use SWITCH function
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 |
#3
|
|||
|
|||
Multiple criteria in Conditional Formatting -- use SWITCH func
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 "strive4peace" "strive4peace2006 at yaho" wrote: 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 |
#4
|
|||
|
|||
Multiple criteria in Conditional Formatting -- Name, spaces
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 * Steve Vincent wrote: 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 "strive4peace" "strive4peace2006 at yaho" wrote: 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 |
#5
|
|||
|
|||
Multiple criteria in Conditional Formatting -- Name, spaces
Crystal, the NZ function clenched it for me. Thank you so much! Have a
wonderful weekend. Steve "strive4peace" "strive4peace2006 at yaho" wrote: 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 * Steve Vincent wrote: 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 "strive4peace" "strive4peace2006 at yaho" wrote: 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 |
#6
|
|||
|
|||
Multiple criteria in Conditional Formatting -- Name, spaces
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 * Steve Vincent wrote: Crystal, the NZ function clenched it for me. Thank you so much! Have a wonderful weekend. Steve "strive4peace" "strive4peace2006 at yaho" wrote: 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 * Steve Vincent wrote: 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 "strive4peace" "strive4peace2006 at yaho" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Custom functions calculating time arguments Help Desperate | Bill_De | Worksheet Functions | 12 | April 25th, 2006 02:22 AM |
Using Sumproduct Function To Add Multiple Criteria | Ange Kappas | Worksheet Functions | 1 | January 23rd, 2006 11:29 AM |
creating a function with multiple criteria | e_bone75 | Worksheet Functions | 2 | October 14th, 2005 09:08 PM |
Conditional Formatting Multiple cells based on 2 cells | Louis Markowski | Worksheet Functions | 2 | June 1st, 2005 05:26 PM |
Countif function based on multiple criteria | Craig Mowbray | Worksheet Functions | 4 | September 28th, 2003 10:42 PM |