Reference field names in expression

[Joallyn]

Hi all, I'm trying to generate a report showing projects and clients in a given month.

All well and good. The bad is that there are Individual clients, Miscellaneous clients, and regular clients. Individual and Miscellaneous clients have an associated name or organization; regular clients stand as they are.

If a client is either Ind or Misc, I want the report to print out the associated detail info.

My first stab at it layered text boxes on top of each other and used a few "If...then" statements to toggle the visibility of the box in question.

Did I stop there? Nooo...I had to try to make it more elegant, so I created an unbound textbox and inserted this expression:

txtClient = IIf(Me![Client] = "Individual", "Individual - " & [LastName] & ", " & [FirstName], IIf([Client] = "Misc", "Misc - " & [MiscClient], [Client]))


(English explanation):

If [Client} is "Individual", the report will show: Individual - {lastname}, {firstname}

If [Client] is "Misc", it will show: Misc - {miscClient}

otherwise it just shows the normal client name.


But the report doesn't recognize any of the field names! I'm totally stumped--how do I access a field that appears in the report's underlying query? It seem like it should be very, very easy....

Any help (or suggestions about the approach) would be very much appreciated!

thanks,

J

 

[Joallyn]

Errr, sorry folks, I solved it, though I didn't entirely answer my own question.

If I put the expression directly into the unbound text box, it works beautifully. If I put it in the Detail_Format section of the report, I get errors about the fields not existing.

Why, I don't know, so if anyone cares to explain it to me, I'd be grateful....

thanks,

J

 

[dhookom]

If this is the controlsource of a text box then don't use Me![Client].
I would set the control source to:
=Switch([Client]="Individual","Individual - " & [LastName] & ", " & [FirstName], [Client]="Misc","Misc...&quot
The Switch() function is much more flexible and easier to maintain than nested IIf()s.

 

[dhookom]

You can't reference fields from within code unless they are bound to a text box. You would have to create a text box "txtClient", bind it to [Client], and the you could reference Me.txtClient from your code.

 

[Joallyn]

Nice! I didn't know about Switch, but it worked perfectly and looks much cleaner.

Thanks for the explanation of the fields, as well. One puzzle down, 1,000,000 yet to solve...

appreciatively,

J