Conditional formatting referencing a sub report

[just_some_guy]

Good morning,

I have two reports. The first one show a score set for the last 30 days. The second, a sub-report, shows the current goal which changes often. I need to do a conditional format that will change the score to red any time it dropped below what is now the current goal.

I cannot figure out how to get the conditional formatting to look at the sub-report to see that it needs to change.

Right now it is set to "Field Value is Less Than [npshuddleqry2]![goal] (npshuddleqry2 being where the sub-report pulls the goal), and it doesn't change the format.

I've verified that both fields are formatted as numbers in their original tables. I don't get any error messages when I switch back to report view.

Any ideas what I'm doing wrong? I figure I'm just referencing the field in the wrong way, but I'm not sure what exactly I'm supposed to have in there.

Thanks in advance!

 

[vbaInet]

You need full reference plus "Expression Is":

Expression Is Forms!NameOfForm!ControlName < Forms!NameOfForm!SubformName!ControlName

However, it's odd that you're checking against a subform, I would have thought you check against the child.

 

[just_some_guy]

vbaInet,

my reference now says

expression is [queries]![nps huddle qry]![nps score]<[queries]![nps huddle qry 2]![Goal]

When I click "OK" on the conditional format window, I get the error message "You can use the IS operator only in an expression with Null or Not Null." If I take out the "Expression IS" I get a parameter box asking for "Queries"

I have changed it again to where it reads

[Reports]![nps huddle rpt]![nps score]<[Reports]![nps huddle 2 rpt]![Goal]

it does not ask for parameters, but once again, there is no change. I tried it again with the Expression IS on the front, and still no luck.

Thanks

 

[vbaInet]

You can't reference a query that way and if you noticed in my example (on the RHS) there were 4 levels.

I think we need more information here. Does the subreport return more than 1 record?

You can use a DLookup() too.

 

[just_some_guy]

The subreport only returns one record. The main report returns 30 or 31, depending.

I'm really sorry, but I'm very much an access novice, so your comment about only having 4 levels is one that I don't understand. Would you mind elaborating?

Lastly, how would I use dlookup in conditional formating? On a side note, I think I had read that using dlookup in reports can be very slow, but they never elaborated on why, so if it's the better way to go, I'll most certainly be willing to.

Thanks again for all your help.

(edit) On closer inspection, I just understood the "four levels" part. I'm going to go give that a shot now!

 

[vbaInet]

DLookup() is just an alternative and yes it is not an optimised function.

Remember to reference the SubreportName (on the right).

By the way, how many fields are displayed in the subreport?

 

[just_some_guy]

vbaInet,

here is my current code, still with no luck.

[Reports]![npshuddlerpt]![npsscore]<[Reports]![npshuddlerpt]![npshuddle2rpt]![Goal]

There are 5 fields in the subreport, 1 record for each field.

Thanks for all your help and patience!

*edit* It is now coming up asking for the value of npshuddle2rpt when I try to switch to report view, with the code I just posted in the conditional formatting.

 

[vbaInet]

Let's see a stripped down version of your db.

 

[just_some_guy]

Here is the stripped down version. Everything seem functional except for that one bit.

Thanks, and if I'm not able to get back to you, have a great weekend.

 

[vbaInet]

Here it is (either one):

Expression Is [npsscore]<[nps huddle qry 2]![Goal]
Field Value Is Less Than [nps huddle qry 2]![Goal]

Have a good one too.

 

[just_some_guy]

vbaInet,

were you able to get this to work on the stripped down version I sent over? I've tried both codes.

When I try the "expression is" code, it is not saving it. I click "Ok" on the conditional formatting box, I click save, go back to the report view and see that nothing has changed. I go back to check the code in conditional formatting, and it is gone from the box as if it were never applied.

When I try the "Field Value" code, it saves the information, but it doesn't highlight any field entries on the report.

Sorry to keep being a bug, but I just can't seem to get this.

Thanks!

 

[vbaInet]

It would work on Print Preview mode but not Report View. Something to do with the way Access loads subreports in report view. I used a DLookup() to get the value in the report's header and referred to that textbox.

See attached.

 

[just_some_guy]

vbaInet,

Thanks so much for that! That works perfectly, using the DLookup, and I honestly never would have figured out how to do that. Now I know for future problems.

Thanks again for all your help. It's a small thing, this whole conditional formatting issue, but it will make my bosses very happy!

 

[vbaInet]

Or just get them to use Print Preview mode. It will make your life much easier!

Glad to help!