Controlling Report Unbound Textbox Content

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

I am using a query to populate a report (letter). I am aving a little
trouble with formatting the address information of this report.

For instance, I used "=[Salut] & " " & [First] & " " & [Last]" as the
control source but my problem is that not all the records have a [Salut]. So
I would need to use it when it is available but ignore it if it is not their.
My current control source places an unneeded space when their is no [Salut]

My other problem was that some people use one line in their address while
others use 2. This once again causes unnecessary blank lines... I know this
is a common task and I'm hoping that someone can show me the way to get a
clean address in a report.

Moreover, while trying to get this to work I tried to use VBA... but it
didn't work? One cannot reference control source fileds from VBA. What
approach needs to be used then? Does one have to place the fileds on the
report invisibly and then through VBA check the control values to perform
operations... or is there an easier, more fficient way?

Thank you,

Daniel
 
You can either make the adjustments in the table that drives the report
(preferable in my opinion) or in the report when it is formatted (use Format
event). You may also be able to use the IIF function e.g.
IIF(NZ(Salut,0)=0,"",Salut)

Your last assumption is correct, you refer to report values via the Name of
the control and not via the column names in the table. Therefore if the value
is not in the report, you need to add a hidden control.

-Dorian
 
Good morning,

I am using a query to populate a report (letter). I am aving a little
trouble with formatting the address information of this report.

For instance, I used "=[Salut] & " " & [First] & " " & [Last]" as the
control source but my problem is that not all the records have a [Salut]. So
I would need to use it when it is available but ignore it if it is not their.
My current control source places an unneeded space when their is no [Salut]

My other problem was that some people use one line in their address while
others use 2. This once again causes unnecessary blank lines... I know this
is a common task and I'm hoping that someone can show me the way to get a
clean address in a report.

Moreover, while trying to get this to work I tried to use VBA... but it
didn't work? One cannot reference control source fileds from VBA. What
approach needs to be used then? Does one have to place the fileds on the
report invisibly and then through VBA check the control values to perform
operations... or is there an easier, more fficient way?

Thank you,

Daniel
Click to expand...

An unbound control with it's control source set to:

=([Salut] + " ") & [First] & " " & [Last]

Will not leave a space if Salut is null,

You could also have used:

=IIf(Not IsNull([Salut]),[Salut] & " " & [First] & " " & [Last],
[First] & " " & [Last])

Regarding using one line or two as an address, you can use the similar
expressions as above. changing the field names. Or, if you want to use
2 separate controls on 2 lines, set their CanShrink property to yes.
Also set the Detail Section CanShrink to yes. As long as no other
control is on that same line, the data will move up if the field is
null.

Regarding using VBA, simply leave the control source of an unbound
control blank. Then you can populate the control using VBA.
Code the Detail Format event:

txtControlName = IIf(Not IsNull([Salut]),[Salut] & " " & [First] & " "
& [Last], [First] & " " & [Last])
 
Back
Top