A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

HELP: Conditional Formatting with more then 3 conditions



 
 
Thread Tools Display Modes
  #11  
Old May 12th, 2005, 08:34 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Using this wouldn't be a Select Case, you would just place the statement in
the code. Also, please see my second reply which I believe would be faster
than the DLookup.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
The colors won't appear. Here is the code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case Me.txtBOX
Case Me.txtBOX.BackColor = DLookup("color", "condition-color",
"condition='" & Me.txtBOX & "'")
Case Else
Me.txtBOX.BackColor = 16777215
End Select

End Sub



  #12  
Old May 13th, 2005, 12:21 PM
Tim
external usenet poster
 
Posts: n/a
Default

nothing worked

here is the code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtBOX.BackColor = DLookup("color", "condition-color", "condition='" &
Me.txtBOX & "'")
End Sub

Am I missing something?

And when I tried the query approach I got an error message:
The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a seperate
query that performs that first join and then include that query in your SQL
statement.


  #13  
Old May 13th, 2005, 04:28 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Ok, the query may not work depending on how complicated the query is that
you already have.

I just tried the DLookup option and it worked. Are you getting any error
messages? Have you stepped through the code to see what values are actually
being found in the code? While the table name doesn't have any spaces in it,
try enclosing it in brackets anyway in case the hyphen is causing a problem,
"[Condition-Color]". To handle items that may not have a match (this will
cause DLookup to return Null) you may want to wrap the equation with Nz.

Me.txtBOX.BackColor = Nz(DLookup("color", "condition-color", "condition='" &
Me.txtBOX & "'"), 16777215)

This will return a white background if DLookup returns Null. The syntax for
Condition is for a Text value, is that correct? You placed the code in the
Format event of the Detail section, is the textbox txtBox in the Detail
section?

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
nothing worked

here is the code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtBOX.BackColor = DLookup("color", "condition-color", "condition='" &
Me.txtBOX & "'")
End Sub

Am I missing something?

And when I tried the query approach I got an error message:
The SQL statement could not be executed because it contains ambiguous
outer
joins. To force one of the joins to be performed first, create a seperate
query that performs that first join and then include that query in your
SQL
statement.




  #14  
Old May 16th, 2005, 01:56 PM
Tim
external usenet poster
 
Posts: n/a
Default

it worked

thank you for your help


  #15  
Old June 21st, 2007, 01:17 AM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.

Thanks!

"Wayne Morgan" wrote:

Ok, the query may not work depending on how complicated the query is that
you already have.

I just tried the DLookup option and it worked. Are you getting any error
messages? Have you stepped through the code to see what values are actually
being found in the code? While the table name doesn't have any spaces in it,
try enclosing it in brackets anyway in case the hyphen is causing a problem,
"[Condition-Color]". To handle items that may not have a match (this will
cause DLookup to return Null) you may want to wrap the equation with Nz.

Me.txtBOX.BackColor = Nz(DLookup("color", "condition-color", "condition='" &
Me.txtBOX & "'"), 16777215)

This will return a white background if DLookup returns Null. The syntax for
Condition is for a Text value, is that correct? You placed the code in the
Format event of the Detail section, is the textbox txtBox in the Detail
section?

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
nothing worked

here is the code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtBOX.BackColor = DLookup("color", "condition-color", "condition='" &
Me.txtBOX & "'")
End Sub

Am I missing something?

And when I tried the query approach I got an error message:
The SQL statement could not be executed because it contains ambiguous
outer
joins. To force one of the joins to be performed first, create a seperate
query that performs that first join and then include that query in your
SQL
statement.





  #16  
Old June 21st, 2007, 02:25 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.



Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.

--
Marsh
MVP [MS Access]
  #17  
Old June 26th, 2007, 06:48 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

Basically, if [coname]=x, then use color per the below. I've setup the
conditional formatting for three, but I can't do the additional due to
limitation.

Red
Lt Blue
Dk Blue
Purple
Green

Thanks for your help!

"Marshall Barton" wrote:

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.



Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.

--
Marsh
MVP [MS Access]

  #18  
Old June 26th, 2007, 09:10 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

The general idea for doing this in the report Detail
section's Format event procedure is something like:

Select Case Me.coname
Case "x"
Me.coname.BackColor = vbRed
Case "y"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "z"
Me.coname.BackColor = vbBlue
. . .
Case Else
Me.coname.BackColor = vbWhite
End Select

If that's too confusing for you, then explain more about the
"x" and anything else you don't follow.
--
Marsh
MVP [MS Access]


cynteeuh wrote:
Basically, if [coname]=x, then use color per the below. I've setup the
conditional formatting for three, but I can't do the additional due to
limitation.

Red
Lt Blue
Dk Blue
Purple
Green


"Marshall Barton" wrote:

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.



Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.

  #19  
Old June 26th, 2007, 11:22 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

Thanks AGAIN for helping on this! Apologies for my ignorance!

"Marshall Barton" wrote:

The general idea for doing this in the report Detail
section's Format event procedure is something like:

Select Case Me.coname
Case "x"
Me.coname.BackColor = vbRed
Case "y"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "z"
Me.coname.BackColor = vbBlue
. . .
Case Else
Me.coname.BackColor = vbWhite
End Select

If that's too confusing for you, then explain more about the
"x" and anything else you don't follow.
--
Marsh
MVP [MS Access]


cynteeuh wrote:
Basically, if [coname]=x, then use color per the below. I've setup the
conditional formatting for three, but I can't do the additional due to
limitation.

Red
Lt Blue
Dk Blue
Purple
Green


"Marshall Barton" wrote:

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.


Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.


  #20  
Old June 27th, 2007, 12:20 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

First, open the report )the one you are using as a
subreport) in Design View. Then click on a *blank* area of
the report section (Detail?) that contains the name text
box. In the properties list, under the Events tab, select
[Event Procedure] from the drop list on the right side of
the OnFormat property. That should open the report's VBA
module and put the cursor in the event procedure, ready for
you to enter the code:

Select Case Me.coname
Case "John"
Me.coname.BackColor = vbRed
Case "Cyn"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "Rod"
Me.coname.BackColor = vbBlue
Case "Antonio"
Me.coname.BackColor = vbGreen
Case Else
Me.coname.BackColor = vbWhite
End Select
--
Marsh
MVP [MS Access]


cynteeuh wrote:

Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

Thanks AGAIN for helping on this! Apologies for my ignorance!

"Marshall Barton" wrote:

The general idea for doing this in the report Detail
section's Format event procedure is something like:

Select Case Me.coname
Case "x"
Me.coname.BackColor = vbRed
Case "y"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "z"
Me.coname.BackColor = vbBlue
. . .
Case Else
Me.coname.BackColor = vbWhite
End Select

If that's too confusing for you, then explain more about the
"x" and anything else you don't follow.
--
Marsh
MVP [MS Access]


cynteeuh wrote:
Basically, if [coname]=x, then use color per the below. I've setup the
conditional formatting for three, but I can't do the additional due to
limitation.

Red
Lt Blue
Dk Blue
Purple
Green


"Marshall Barton" wrote:

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.


Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting in form slows down calculations Susan Using Forms 5 June 13th, 2005 11:24 AM
Conditional Formatting Error ddate Worksheet Functions 0 May 5th, 2005 09:00 PM
Conditional Formatting Anomaly Rutgers_Excels General Discussion 1 November 1st, 2004 09:43 PM
Freezing Conditional Formatting Ashish Chamaria Worksheet Functions 3 December 19th, 2003 11:54 AM
Conditional formatting with formula Ann Scharpf Worksheet Functions 5 October 21st, 2003 04:29 AM


All times are GMT +1. The time now is 08:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.