[Last Call] Learn how to a build a cloud-first strategyRegister Now


Hide empty textboxes in reports and shift up the others

Posted on 2004-11-23
Medium Priority
Last Modified: 2008-02-01
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?

Question by:trinbago
LVL 41

Accepted Solution

shanesuebsahakarn earned 150 total points
ID: 12655094
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))

Assisted Solution

by:Eric Flamm
Eric Flamm earned 75 total points
ID: 12655781
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)


Author Comment

ID: 12656514
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)
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 41

Expert Comment

ID: 12656578
> 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.

Expert Comment

by:Eric Flamm
ID: 12657128
Yes-splitting points is possible - you're given that option when you close the question.

Happy to help.


Expert Comment

ID: 12697152
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question