undesirable space in label report 3

[shelron]

=Trim([SAL] & " " & [FNAME] & " " & [LNAME]
=Trim([TITLE] & " " & [FIRM_NAME])
=Trim([STREET1] & " " & [STREET2])
=Trim([CITY] & ", " & [STATE] & " " & [ZIP])


I have the above in an Access 2000 report.

It prints labels, I want it to drop the title and firm name if there is no data and move the street and city line up.

Currently I get:

The Honorable John Smith
Mayor Village of Ballston Spa
123 Main Street
Ballston Spa, NY 12020

when I have full data,

I get:

The Honorable John Smith
UNDESIRABLE SPACE HERE
123 Main Street
Ballston Spa, NY 12020

when no data exists for title and Firm name



Is there a way to make the text boxes come together?

Ron

 

[hmm2]

Set the CAN SHRINK property for each of the fields on your report to YES. (But make sure the CAN SHRINK property of the DETAIL SECTION is still set to NO.)

That worked on my labels.

Hope it helps.
-hmm
-Using AccessXP

 

[shelron]

Tried that,but no luck, thanks for the thought,

anyone else have any ideas?

Ron

 

[SgtJarrow]

Instead of four text boxes bound as you have them, create one text box, set the can grow and can shrink property to yes (unless you always want the label the same size...probably the case), and then place the first set of the below code in a module and the rest as the control source for the text box...

'*************Code for Module*************
Private Function FixLine(varValue as Variant) As Variant
' If varValue is Null, FixLine returns Null
' Otherwise, it returns the field value passed in
' with a Cr/Lf on the end.

FixLine = varValue + vbCrLf
End Function
'*************End Code********************

=FixLine([SAL] & " " & [FNAME] & " " & [LNAME]) & FixLine([TITLE] & " " & [FIRM_NAME]) & FixLine([STREET1] & " " & [STREET2]) + FixLine([CITY] & ", " & [STATE] & " " & [ZIP])


"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[shelron]

Thanks very much, that solved the problem nicely.

Ron

 

[boxboxbox]

I tried this with my own information, but keep getting a parameter box wanting to know "FixLine". If I put in information or if I leave it blank, I just get "error" where my text should be. Grr...

 

[idd]

Mstrmage,

that post of yours is excellent. I had tried many things to sort the labels out but nothing worked until, I tried your solution given above.

Works a treat.

Thanks for your help,

your ideas and time is appreciated.

Idd

 

[idd]

BoxBoxBox,

I think what you need to do is put the code in the forms module rather than a global module.

I had the same error when I placed the code in a global Mod but then transferred it to the forms Module.

Idd

 

[boxboxbox]

Idd,

Thanks much, that solved that problem. But now it will only show the first two "lines"...? I know they're not null...

 

[boxboxbox]

Nevermind...I had to change the (+) symbol from the code above to an actual ampersand--that seems to have fixed it. Thanks again, Idd and mstrmage1768!