Using IIF in report header

[Consonanza]

I am trying to insert a display field in the section header of my report. Note that this field is not the groupig section for the report but the components arein the dtaset for the report.

This what I am seeking to achieve:



=IIf([fldLocation] = "n/a","Not assigned","[fldSection] & " - " & [fldLocation]")

The True part works as expected but for the false part, I am getting #Type!

If I just put [fldSection] & " - " & [fldLocation], as the Control Source, there is no error but of course, I lost the conditional element.

[Micron]

try
=IIf([fldLocation] = "n/a","Not assigned",[fldSection] & " - " & [fldLocation])

[Consonanza]

Same error as in my original post :-(

The error message behind the control's exclamation mark says "Circular Reference" if that helps.

[Micron]

Show some values (inputs) and expected outcomes. Pairing the quotes in this: "[fldSection] & " - " & [fldLocation]" wpuld appear to be an attempt to subtract one thing from another. However, it's doubtful that you have anything named "[fldSection] & "

[Welshgasman]

Try

[fldSection] & " - " & [fldLocation]

[Consonanza]

[fldSection] is a text field
Values can be
A 1
A 2
B 1
B 2
Etc
If no Section is assigned a value, it would have a value of "n/a"

[fldLocation] is also a text field with values such as


Back wall (study)
Side wall (study)
Etc


Both fields are contained in 1 table (tblLocations)


Each fldLocationID would have a fldSection eg A1 and a fldLocation eg Back wall (study)


Desired output in the report header would be like


A1 - Back wall (study)
or
F3 - Side wall (study
Etc

[Consonanza]

@Welshgasman - Thanks - same suggestion as Micron.

[Micron]

I think it's time for seeing a compacted and zipped copy of your db because some things just don't make sense. Leave just enough in to replicate the problem if that helps. If privacy is an issue, consider https://www.accessforums.net/showthread.php?t=77482
There is another version of that which often gets referenced but I don't have a link for it and I don't know what the difference is either.

[Consonanza]

I attach a much stripped down version of the db with just enough data to highlight the problem (note that there is a "no such field" error flag is the report design - not there in the original design but doesn't seem to affect the results - and I was rushing to get this db posted).


ReportGrouping.accdb.zip

[Welshgasman]

Not even sure how the report works, as the recordsource for the report is not even executable?
Having amended that I tried the expression in the query and that works with

Section: IIf([fldLocation]="n/a","Not assigned",[fldSection] & "-" & [fldLocation])

However I think your report is corrupted, as I created a simple report and used the expression field in the group header and that works.

Your report prompts for Section.

Added a new control in my report with source as IIf([fldLocation]="n/a","Not assigned",[fldSection] & "-" & [fldLocation])
and that works fine.

[Consonanza]

.................the recordsource for the report is not even executable?

However I think your report is corrupted.....
Your report prompts for Section.

Not sure I understand. The report works fine for me (I even downloaded it again from this site). The report can be opened directly.

You say the report prompts for Section. What do you mean? When the report opens, you can of course see that there is no value shown for the group header (Section). That is what I am seeking to correct.

If you read my earlier posts, you wil see what the desired output should be. If it helps clarify your point you can upload your version my sample db with your modified report .

[Welshgasman]

I created the report on another computer.
Here is the source of your report

SELECT tblDiscography.fldDiscNo, tblDiscography.fldDiscTitle, tblDiscography.fldLabel, tblDiscography.fldReference, tblDiscography.fldClass, tblRecordStorageLocations.fldSection, tblRecordStorageLocations.fldLocation,tblRecordStorageLocations.fldSeqSort1_Section FROM tblDiscography LEFT JOIN tblRecordStorageLocations ON tblDiscography.fldLocationID = tblRecordStorageLocations.fldLocationIDWHERE tblDiscography.fldDiscNo>0 AND tblDiscography.fldClass LIKE 'L'

See anything wrong with it?

Section was the name I gave to the IIF expression in the query.
I have had to create a quick report again as I am now on my laptop.

The report section header has the query IIF() on the left and the same IIF() as control source on the right.

You can take it from there.

[Consonanza]

Thanks for your time in looking at this.

You posted some code for strSQL and ask if I can see the error - of course I can see that there is no gap before the WHERE clause but that code is not the one produced by the debug.print for the code behind my form.

I have looked at the code behind your version of my report. Whilst I agree it works, it is an oversimplification. I can find no way to translate your suggestion into the more complex VBA that underpins my original report.

I’ll look at an alternative option for my report, so we’ll have to leave this as an unresolved problem.

[Micron]

I fixed the WHERE part in the report recordsource, renamed the control because you can't have a calculated control refer to itself, then tested. Still got the circular reference error although there was no such reference. Removed the expression from the control, saved, reopened in design, put the expression back and all seems well. So the correct answer was given long ago, but the control name was the main issue.

=IIf([fldLocation]="n/a","Not assigned",[fldLocation] & "-" & [fldSection])

[Welshgasman]

Thanks for your time in looking at this.

You posted some code for strSQL and ask if I can see the error - of course I can see that there is no gap before the WHERE clause but that code is not the one produced by the debug.print for the code behind my form.

I have looked at the code behind your version of my report. Whilst I agree it works, it is an oversimplification. I can find no way to translate your suggestion into the more complex VBA that underpins my original report.

I’ll look at an alternative option for my report, so we’ll have to leave this as an unresolved problem.

That sql is not for a form, it is the record source of the report.

it is an oversimplification

Not sure I understand that. It is your query sql code, just as a qdf, and an added field that has your expression.
You can just as easy use the sql code and add the field again.?