How to skip "blank fields"?

[winkmich]

Hi there,

I have an ADDRESS field in on of the columns of the report. It is split up into 4 parts (lines). mostly the addresses just need 2 lines, the other two are empty.

When placing the items the address field takes, of course, the room for 4 lines.

Is there any trick to check whether a field is empty (using IIF or so) before the print?

Or do I have to merge the 4 lines in a query before the report?

In case this is the only option, how can I include a "Carriage Return".

If I use this:
IIf([AddressLine11]<>"",[AddressLine11]+Chr(13),"")+IIf([AddressLine12]<>"",[AddressLine12]+Chr(13),"") ...

then this comes out:

Collingwood HouseTraining & Education CentreHorton Road

Instead of the "Carriage Return" (to create a line break) a "" was added.

Does anyone knows to deal with this problem - either the first or the second way?

Thanks in advance, Michael.

 

[cpod]

You have to use a Carriage Return *and* a Line Feed. You can put this in an unbound textbox on the report (remember to set the "Can Grow" property to yes).


IIf(isnull([AddressLine11]),"",[AddressLine11]+Chr(13)+chr(10))+IIf(isnull([AddressLine12](,"",[AddressLine12]+Chr(13)+chr(10)) ...

 

[RichMorrison]

You can embed a sub report for Address lines. If you make the sub report control 1 line high and set Can Grow to Yes, then any blank address lines will not appear on the sub report.

You want to make a separate query as the RecordSource of the sub form. This query would include some link field and a variable number of address lines. The link field must also be in the query that is the RecordSource of the main form.

HTH,
RichM

 

[Pat Hartman]

You can format the detail section with fields on multiple lines similar to a label. Set the CanGrow/CanShrink properties of all the controls to Yes. Also set the CanGrow/CanShrink properties of the Detail section to Yes.

If ALL the controls on a single line are blank, Access will suppress printing the line. Make sure that none of the controls overlap and if you have labels that need to be suppressed, change their visible property to False.