asked on

Hide empty textboxes in reports and shift up the others

hi guyz

In my Access report I have an address section made up of a couple of txtboxes, like:

txtName
txtName1
txtAddress
txtAddress2
txtPostal Code

Etc...

If the value of txtName1 is empty, I would like to shift up the other txtboxes to prevent a gap in my addressline.. How can I do this most easy way?

Thnx!
Martin

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Eric Flamm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trinbago

ASKER

Eflamm you're right. The first code is incomplete. I like both ideas, but do not have the time now to try the second one with separated VBA.. :-(

I solved the issue with the folowing code pasted in a textbox: (And kept the original textboxes hidden in the report to be able to retrieve their values:)

=[txtName] & IIf(IsNull([txtName2]);"";(Chr(13) & Chr(10)) & [txtName2]) & IIf(IsNull([txtAddress]);"";(Chr(13) & Chr(10)) & [txtAddress]) & IIf(IsNull([txtAddress2]);"";(Chr(13) & Chr(10)) & [txtAddress2]) & IIf(IsNull([txtPostalCode]);"";(Chr(13) & Chr(10)) & [txtPostalCode]) & IIf(IsNull([txtCity]);"";(Chr(13) & Chr(10)) & [txtCity]) & IIf(IsNull([txtCity2]);"";(Chr(13) & Chr(10)) & [txtCity2]) & IIf(IsNull([txtCountry]);"";(Chr(13) & Chr(10)) & [txtCountry])

So I guess no 1 gets my points (is splitting points possible? Im not sure)

shanesuebsahakarn

> each vbCrLf will create a blank line if the text field is blank.

True - I am assuming that the fields are Null and not empty strings. If they are Null, the solution will work, but empty strings will cause spurious lines, in which case an If/IIf test is required.

Eric Flamm

Yes-splitting points is possible - you're given that option when you close the question.

Happy to help.

-ef

ulatif

you can use the onNoData event in a report and set such text boxes to invisible (this will automatically shift others up).

you can probably do the same in onActivate event call