If Statement in Report Field 3

[fredk]

I have an unbound field on a report that I use to show if the report is for "new business" or "conversions" - I basically have an if statement in the form that refers to the input form and if they selected "n" then "new business" appears on the report, otherwise "conversions" appears

I used =if([Forms]![frmMainReport]![norc]="n","New Business","Conversion&quot

However, I have added the option to show both New and Conversions in the report.

How do I add to the unbound box so that I can have a third criteria that states "Both New and Conversions" if they selected Both on the input form?

Thanks for the assistance!!!!!

Fred

 

[PaulBricker]

=IIf([Forms]![frmMainReport]![norc]="n",IIf([Forms]![frmMainReport]![norc] = "c","Conversions","Both New and Conversions&quot)

That should do it.

Paul

 

[SgtJarrow]

Again, while Paul's IIF statement should work, I find that any IIf statement that has more than two options really is easier to maintain with a select case function....It is easier to read and easier to update later on...

Put the following into a new module:

Public Function GetValue(strInput As String) As String

Select Case strInput
Case "c": GetValue = "Conversions"
Case "n": GetValue = "New Business"
Case Else: GetValue = "Both New And Conversions"
End Select

End Function

Then, in control source of the field on the report, put:

=GetValue([Forms]![frmMainReport]![norc])

You can add more Case statements to the function as necessary, and if the Select does not find any Case specifically for the entry, the Else statment is used...

Much easier to maintain and read....Enjoy.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[fredk]

Thank you both for your replies - I had initially went the route that Paul suggested - I will add the select case as a function

Thank you very much - I continue to learn so much - I really do appreciate the assistance!!!!

Fred

 

[toolman2]

Robert,
I know this is old, but it helped me out tremendously. Been looking for such a solution and found it here.
Thanks again
Kevin

 

[SgtJarrow]

Toolman2,

Thanks. I am glad you took advantage of this older thread and found your answer. I am always available for assistance.

Robert

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com