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:

txtPostal Code


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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

An expression like so:

[txtName] & (vbCrLf+[txtName1]) & (vbCrLf+[txtAddress]) & (vbCrLf+[txtAddress2]) & (vbCrLf+[txtPostalCode])

If this is done in a query, replace vbCrLf with:
(Chr(13) & Chr(10))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric FlammSenior ConsultantCommented:
I don't think that solution does the trick - each vbCrLf will create a blank line if the text field is blank.

I assume you're doing this in a report - to create mailing labels, or whatever. Unfortunately, the usual text formatting approaches aren't parsed by the report writer, and you end up with some unprintable characters (empty boxes) instead of neatly formatted text. I've used a VBA function to organize my text, then populate the text box, like so:

Public Function Address(A1 As String, A2 As String, Code As String, N1 As String, N2 As String, City As String, ST As String) As String

Address = ""
Address = Address & N1 & vbCrLf
If N2 <> "" Then Address = Address & N2 & vbCrLf
Address = Address & A1 & vbCrLf
If A2 <> "" Then Address = Address & A2 & vbCrLf

...etc - you get the idea...

End Function

Then, in your text box, your control source is =Address(Address1,Address2,Code, Name1,Name2,City, State)

trinbagoAuthor Commented:
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)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

> 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 FlammSenior ConsultantCommented:
Yes-splitting points is possible - you're given that option when you close the question.

Happy to help.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.