Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-29-2019, 04:09 AM   #1
Suzi
Newly Registered User
 
Join Date: Aug 2019
Location: Durham, UK
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Suzi is on a distinguished road
Conditional formatting of a report field based on outcome of two fields (Access 2016)

Hello everyone!

Long time reader, first time poster!

I have done several searches to find the answer I need to my conditional formatting nightmare but I can't find an example which matches mine and my own thoughts aren't yielding anything.

I have two fields: "car reg" and "external". If car reg is blank and external is true I want the car reg field on a report to have a shaded background.
I've tried many different ways and none have worked.

Currently, in my conditional formatting box I have:

Expression is: ([tblContacts.External]=True) And ([tblContacts.Car Reg] Is Null)

but no amount of changing 'true' to Is not null, 1, "true" and all of the other iterations I can think of changes the colour of the cells!

Please could any of you super helpful people put me out of my misery?

Suzi

Suzi is offline   Reply With Quote
Old 08-29-2019, 04:33 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

I have never used the table reference and field, always the control names.?

Code:
[Sum of Amount]>100
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 08-29-2019, 07:03 AM   #3
Suzi
Newly Registered User
 
Join Date: Aug 2019
Location: Durham, UK
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Suzi is on a distinguished road
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

Quote:
Originally Posted by Gasman View Post
I have never used the table reference and field, always the control names.?

Code:
[Sum of Amount]>100
Thanks Gasman, Unfortunately the field aren't numerical; one is a true/false and the other is a text field. I simply need to know if the true/false is true AND the car reg field is blank that it highlights the field on the report to me to remind me to get the reg number.

It's probably the car reg value that's causing the expression to not work. How do we represent a null value in an expression?

Suzi is offline   Reply With Quote
Old 08-29-2019, 07:36 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

Don't be concerned about the type of control, that was just an example.?
Test the controls on the report not the table fields.

You could use IsNull() or Nz() functions?

Code:
Not IsNull([Sum of Amount])
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 08-30-2019, 03:27 AM   #5
Suzi
Newly Registered User
 
Join Date: Aug 2019
Location: Durham, UK
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Suzi is on a distinguished road
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

Sorry Gasman, I'm not really sure what you mean.

here is a screenshot of what I have:
Attached Images
File Type: png conditional formatting1.PNG (10.8 KB, 13 views)
Suzi is offline   Reply With Quote
Old 08-30-2019, 05:43 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

I've already stated that I have never used the field names, only control names.
Copy that code you already have and paste into notepad or anywhere you can save the old version.
Then use the control names and try that.

if that does not work, you can always paste the old formula back, should you wish.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 08-30-2019, 06:16 AM   #7
Suzi
Newly Registered User
 
Join Date: Aug 2019
Location: Durham, UK
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Suzi is on a distinguished road
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

Okay thank you, I get that you're saying I'm using the wrong wording but where do I find what the control name is please...?

Suzi is offline   Reply With Quote
Old 08-30-2019, 07:58 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Conditional formatting of a report field based on outcome of two fields (Access 2

open your report in design view.
click on the [car reg] or [extrenal] textbox.
on its property->others, you will see their Name.
try renaming them also, txtCarReg and txtExternal.

on your CondFormat Expression:

(Nz([txtCarReg], "") = "") And Nz([txtExternal], False)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
access 2016 , conditional formatting

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Conditional formatting in Access 2016 Hoppy1958 Tables 4 01-11-2019 04:25 AM
Conditional formatting based on two fields thecrowtv1 Reports 5 02-17-2015 12:53 PM
Conditional Formatting on Multiple Fields based on Two Criteria Tophan Reports 4 09-24-2014 06:59 AM
Subform Conditional Formatting based on comparison between two fields Massoud Forms 1 06-27-2011 11:45 PM
Summing fields based on the outcome of a yes/no field thebigdit Reports 4 05-30-2007 06:59 PM




All times are GMT -8. The time now is 12:13 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