the Name field in the table is text and the data is alpha + numbers, so if the data is 4000 for example, I want it to print 4000 on the line on the report...
If the data is TOTAL then I want it to print N/A.
It is not working properly, all it will print out is N/A on ONE line and then blanks on all the rest...
This is a common misunderstanding about MS Access. When a report is built by the report wizard it makes the textbox name and the data name the same. When you change the data value to be a formula, EVERYONE forgets that they MUST change the object name to something 'neutral', like txbValue121. Otherwise internally access creates something like circular references.
Go to design mode, right click on the control and select properties. Select the Other tab and change the name property to ANYTHING but [Name]! Congratulations on using the good practice of using braces around field names. It is a "best practice".
Still no luck with this IIf statement. So I will give you as much info as I have.
This is on a report, in the control source under the data tab of the text box:
=IIf([Name] Like "4*",[Name],"N/A")
However the result on the report shows N/A under the first detail record, which is wrong because the first name is 4000. And the other 5 detail records are all blank on the report eventhough they should be 4001, 4002, etc.
Now if I change it to this:
=IIf([Name] Like "*",[Name],"N/A")
which should print the name which is 4000, instead it prints the name of the report in the first detail record and the rest are blank
If I just leave [Name] in the control source it will print detail records
Instead of Restricted or any other data printed, I want it to print N/A
Sorry I didn't catch the problem earlier!
In a Report, and probably in a form also, a control by the name of "Name", even if you reference it by [Name] is NOT accessible!
If you create a control with the ControlSource of =[Name] what will print is the Name property of the Report!!! Easy FIX --> re-name the field in the query. SELECT Name as MyName... then use =[MyName].
Being able to specify a scope is very handy! Where name collisions like this occur it is ALWAYS nice to be able to specify which one you want!