Hide empty textboxes in reports and shift up the others

Posted on 2004-11-23
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

    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))
    LVL 8

    Assisted Solution

    by:Eric Flamm
    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

    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)
    LVL 41

    Expert Comment

    > 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.
    LVL 8

    Expert Comment

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

    Happy to help.


    Expert Comment

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now