Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-14-2012, 01:34 PM   #1
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Creating a conditional format in a report with Expr1

I'm trying to get a cell to highlight in a Report. On the Name and Source I see Expr1, Expr2 and so on. The numbers are being pulled from a Query whose data is obtained with a calculated control. [Obstetrics This Midnight] + [Obstetrics OPO Midnight] + [OB Same Day Stay]. On the report, I want the cells to highlight where the OB Same Day Stay is >0. Any suggestions? I'm not terribly familiar with code writing so be kind...

corbimae is offline   Reply With Quote
Old 11-14-2012, 01:41 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

No code necessary. Check out Conditional Formatting on the ribbon (or Format/Conditional Formatting pre 2007).
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-14-2012, 02:27 PM   #3
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Re: Creating a conditional format in a report with Expr1

actually I did go into conditional formating. I can get the entire column to highlight if I key in something like ([Obstetrics Same Day Stay] >0) Out of 31 days in October our OB department only had 1 Same day stay and that was 10/3. What I'd like to happen to only have that day highlighted. I read where I needed to change the name to something other than the established control or field but that didn't work either. When I pull up the property sheet the Name is Expr1 and the Source is Expr1. It doesn't list the entire equation. That being the case, can Access still see if the Same Day value is greater than 0? Actually it's not jut the OB department that I need to set up. It's also Medical, Surgical, ICU, etc. Any suggestions would be appreciated. The control in the Query is the one I listed in the original post. Also in the conditional formatting I used the first condition to define the control, then the second condition was to select the control from the Daily Census Report Table and then the third condition was ([OB SD] >0). Everything I've tried has been either the entire column has been highlighted or none of its been highlighted.

corbimae is offline   Reply With Quote
Old 11-14-2012, 02:31 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

Can you post the db here?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-14-2012, 05:49 PM   #5
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Re: Creating a conditional format in a report with Expr1

I don't have the db here so I will try to do it tomorrow when I finish with patients.
corbimae is offline   Reply With Quote
Old 11-15-2012, 01:13 PM   #6
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Re: Creating a conditional format in a report with Expr1

I Think its attached.
Attached Files
File Type: zip New Compressed (zipped) Folder.zip (232.3 KB, 50 views)
corbimae is offline   Reply With Quote
Old 11-15-2012, 01:26 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

I'm not really clear on the required condition, as all the values in that column are >0, but see if the attached gets you anywhere.
Attached Images
File Type: jpg CF.jpg (97.1 KB, 80 views)

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-15-2012, 01:29 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

Wait, I think I see now. Add the [OB SD] field to the query and then use this condition:

[ob sd]>0

You got prompted for it before because it wasn't a field in the query.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-15-2012, 01:46 PM   #9
fmargara
Newly Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
fmargara is on a distinguished road
Re: Creating a conditional format in a report with Expr1

Hello. I asked this question in an old thread with no response.
I am trying to get duplicate names to highlight in my report also. I can't get it to work. We have Access 2010 now. I am using this expression in the conditional fromatting;

Nz(DCount("*","Formal Training Information 2 Query","[Name]='"&[Name]&"'"),0)>1

Am i missing something? Thanks in advance.

Last edited by fmargara; 11-15-2012 at 01:52 PM.
fmargara is offline   Reply With Quote
Old 11-22-2012, 03:55 PM   #10
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Re: Creating a conditional format in a report with Expr1

Thanks Paul, I'm still battling this thing. I know its some little something that I've missed...I finally understood the necessity of renaming the control and how not doing so can cause the Enter Parameter prompt. I renamed my controls by adding txt to the beginning as I read something about that on a Help article. I then went back through each Control Source and using the Expression Builder confirmed that I was using the Control from the Original Query and Not the new control names. Still when I go back in and enter the [OB SD]>0 I get something but a parameter prompt. Any other suggestions? BTW I do appreciate your help.
corbimae is offline   Reply With Quote
Old 11-22-2012, 04:41 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

did you do this part?

Quote:
Originally Posted by pbaldy View Post
Add the [OB SD] field to the query
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-23-2012, 07:47 AM   #12
corbimae
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
corbimae is on a distinguished road
Re: Creating a conditional format in a report with Expr1

The report is being created by Access using the original query. In the Query pane the Expression reads as follows: Expr4:[Daily Census Report].[Obstetrics This Midnight]+[Obstetrics OPO Midnight]+[OB SD] (syntax may be wrong but you get the gist). To get the report I highlighted the query title in the Menu and then using the Create ribbon I click report. From there Access is creating the report and the property control source is simply Expr4. I renamed the control to txtExpr4 and left the Control source as Expr4. The Record Source says Monthly Tally for Mr Gambrell which is the name of the query. If I go into conditional formatting and use Expression Is: [Expr4] and ask it to highlight yellow Access Reads the Expression as True lights up the column. I did this as a test to make sure that it did recognize the original control. My next test was to elminate the yellow background for [Expr4] and add a second condition that was Expression Is [OB SD]>0. When I do that it leaves the background white and it asks for a parameter value when I open and close the report. I have also tried to write out the entire expression for the control property but when I close and re-open the report I still get the Parameter prompt. I guess the answer to your qustion of did I add [OB SD]>0 to the query is yes. This would probably be simpler if I just created my own report but it maddens me that I can't figure this out. I've read so many articles on conditional formatting that they're starting to run together. Somewhere in the programming where Access converts the query into a report there is some change that I can't see. If it recognizes the Expr4 then logically it should understand the calculation it defines. However, if I write out the Expression on the control source it asks for parameter value. At one point, I wondered if Access had changed something on the Record Source but everytime I check it just says Record Source: Monthly Tally for Mr Gambrell. It's very frustrating
corbimae is offline   Reply With Quote
Old 11-23-2012, 10:56 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Creating a conditional format in a report with Expr1

The query returns a field named Expr4 to the report. The [OB SD] field needs to be added as a field by itself so the report can use it.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-27-2012, 08:31 AM   #14
fmargara
Newly Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
fmargara is on a distinguished road
Re: Creating a conditional format in a report with Expr1

Hello. I asked this question in an old thread with no response.
I am trying to get duplicate names to highlight in my report also. I can't get it to work. We have Access 2010 now. I am using this expression in the conditional fromatting;

Nz(DCount("*","Formal Training Information 2 Query","[Name]='"&[Name]&"'"),0)>1

Am i missing something? Thanks in advance.

fmargara is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format for Report Help Jennifer Access Reports 18 11-16-2011 02:59 PM
Conditional format possible in report? phil4 Reports 4 11-15-2011 07:57 AM
Conditional Format Date on Report fenhow Forms 6 03-06-2008 12:37 PM
conditional format within a report grecla General 7 06-28-2004 12:23 PM
Conditional Format on my Report RichB Reports 3 12-13-2003 09:14 AM




All times are GMT -8. The time now is 10:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World