Removing gap from report when there is no value

[crhodus]

I have an address section on a report that contains the following fields displayed in this way on my report:

address_1
address_2
city state country zip

Is there any way that I can have the line that contains the fields "city state country zip" move up a line and replace the address_2 field, whenever there is no value to be displayed in address_2?

Thanks.

 

[Rich@ITTC]

Hi crhodus

Yes.

Set the CanShrink Property for the text box address_2 (which ought to be called txtAddress2 really) to Yes.

HTH


Rich Gorvin

 

[KevinM]

Here's an even better way...

Create a new module and pate this in...

Function AddressBlock$(Addr1, Addr2, Addr3, Addr4, Addr5, Addr6, Addr7)

Dim A1$, A2$, A3$, A4$, A5$, A6$, A7$, CR$
CR$ = Chr(13) & Chr(10
A1$ = IIf(ISB(Addr1), "", Addr1 & CR$)
A2$ = IIf(ISB(Addr2), "", Addr2 & CR$)
A3$ = IIf(ISB(Addr3), "", Addr3 & CR$)
A4$ = IIf(ISB(Addr4), "", Addr4 & CR$)
A5$ = IIf(ISB(Addr5), "", Addr5 & CR$)
A6$ = IIf(ISB(Addr6), "", Addr6 & CR$)
A7$ = IIf(ISB(Addr7), "", Addr7 & CR$)
AddressBlock = A1$ & A2$ & A3$ & A4$ & A5$ & A6$ & A7$ 'Concatenate the strings.
End Function

Function ISB(V) As Integer
If IsNull(V) Or V = "" Then ISB = True Else ISB = False
End Function

NOTE: This is for SEVEN Address fields so you will need to adjust it to the number of fields you require.

Creat a text box in your report and resize it to the the approx size of ALL your address fields. In th conttolsource put....
=AddressBlock ([AddrFieldName1],[AddrFieldName2],[AddrFieldName3],[County],[Zip] etc)
The number of fields MUST match the number of fields in the above function, seven in this example.

This method is MUCH neater than setting several fields to can shrink as there will always be a gap an uneven gap in between each depending on which are null.

HTH

Kevin M

 

[KevinM]

When I say "better" I mean that it looks neater on the report, having all the address fields concanated in one text box.
I agree that with only two or three address fields you may just as well use the built in CanShrink propety of each text box, but with more fields the CanShrink property makes the gaps in between each look uneven depending on which and how many are null.

I use the function an all my dbs that store addresses (I have yet to design one which uses only three fields)

Looking at crhodus's original post it looks like he/she has more fields than just the three shown, I would imagine "city state country zip" are concanted from separate fields which is not the usual way of displaying an address on a report. So in which case I think "my" Function (courtesy of A97kb) would be crodus's "better" option.

 

[jeff_i]

This might be a really stupid question but what do the $ mean I don't get understand the purpose of them.

Thanks

 

[mike7]

How would you do this in a mail merge ?

Same question, but in a letter done by mail merge...How would you skip address2 field if null and not get a gap?

Do you have to modify the query...?

On a label, I know I can apply the CanSrink property, but the letter...


Thanks

 

[adamburton]

Handling blanks in address fields on reports

For Pat and Rich,

I have the same problem with wanting my report address fields to shunt upwards when there is no value.

I have set my CanShrink property to Yes. Now sometimes the gaps disappear but others will not. I have checked these fields and none of them have values (even spaces) in, so am confused. Any suggestions?

Thanks in advance,
Adam.

 

[adamburton]

Thanks Pat

Thanks for your reply Pat.

I figured out the problem - I had a couple of fields on the right side of the report, at the same height. They were stopping the fields shifting up.

But your advice about getting all the fields close together was very helpful too, and now it all works.

Thanks again.
Adam.

 

[suepowell]

Hi,

This is the method I have used for years,

I create a new text box and concatenate the address fields I have using the iif statement to leave them out if they are blank, a bit like KevinM's approach.

So I have 1 text box with something like this as the record source:

=[customeraddress1] & "
" & IIf(IsNull([customeraddress2]),"",[customeraddress2] & "
") & IIf(IsNull([customeraddress3]),"",[customeraddress3] & "
") & IIf(IsNull([customertown]),"",[customertown] & "
") & IIf(IsNull([customercounty]),"",[customercounty] & ", ") & IIf(IsNull([customerpostcode]),"",[customerpostcode] & "
") & IIf(IsNull([customercountry]),"",[customercountry] & "
")

depending on your version of Access you can put the carrage return directly into the statement (as above), or you may need to use chr(10) and Chr(13) for carrage return and line feed.

I then set the can grow and can shrink properties of the text box to both be yes. This means I don't have to worry about spacing between my boxes, and the spacing is not affected by any other controls which might be on the report at the same height.

One note of caution, you must create a new text box, not use a field pulled straight from your table. If you do you end up with a text box you are trying to give a value to with the same name as a field in the table, and Access just doesn't like it

Hope this gives you another possibility.

Sue

 

[fibayne]

Hi.. Just used KevinM's solution and it worked a treat...cheers Fi

 

[stopher]

Just for info..

There's a neat trick using the + operator to avoid the use of IIF:

([AddressLine1]+(Chr(13) & Chr(10))) &
([AddressLine2]+(Chr(13) & Chr(10))) &
([AddressLine3]+(Chr(13) & Chr(10))) &
([AddressCity]+(Chr(13) & Chr(10))) &
([AddressCounty]+(Chr(13) & Chr(10))) &
([AddressPostcode]+(Chr(13) & Chr(10))) &
([AddressCountry]+(Chr(13) & Chr(10)))

+ can also be use as a concatenator as well as &. The difference is if either side of the operator is a null then the + makes the result null and hence no blank line.

Chris