Solved

Skipping a line for null data

Posted on 2012-12-21
5
308 Views
Last Modified: 2013-03-06
I am creating a report in Access to show a list of Contact Names, addresses, phone #s, etc.  I have 2 lines for Address and then Address2 and I am trying to not have a blank line show for people who do not have an Address2.  I'm sure this is simple I just forgot how to do it.
0
Comment
Question by:apsonline
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Expert Comment

by:rshq
ID: 38713981
Hi
 Maybe you can use shrink.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 38714540
The Can Shrink property for your Address2 textbox will work provided that you don't have any controls next to that textbox.  If you do have any other controls horizontally lined up with it, the Can Shrink property will not work.

To avoid problems with skipped lines, I generally use one textbox for both address lines, and set it's control source property as follows (including the = sign):

= Address & iif("" & Address2 = "", "", Chr(13) & Chr(10)  & Address2)

Open in new window


If
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 167 total points
ID: 38714942
Try this expression in a textbox:
=[LName] & ', '+[FName] & '
'+[Address1] & '
'+[Address2] & '
'+[City] & ', '+[State] & ' '+[Zip] & '
'+'Home: '+[HomePhone] & '
'+'Cell: '+[CellPhone]


At the end of each line (after each single quote), add a carriage return using CTRL+ENTER.  The + sign concatenates the fields and if a field is null it erases any concatenated strings including carriage returns.  So if the only fields with data were First & Last Name, City, and CellPhone, it would show up as:

Skywalker, Luke
San Diego
Cell: 123-456-7890


You can learn more about this concatenation style form harfang's article:
Concatenation in Access Reports (and Forms)
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 166 total points
ID: 38716081
The best thing (though maybe not practical at this point) would be to eliminate the Address2 field entirely.  There is no need for a field for each line of an address, since you can enter multiple address lines into one field in Access.  If that is not practical, you can use something like the following in a query expression:

Address: IIf(Nz([Address2])<>"",[Address1] & Chr(13) & Chr(10) & [Address2],[Address1])
0
 

Author Closing Comment

by:apsonline
ID: 38959414
I tried all three of these and they all worked somewhat, however the =Trim command, which was not suggested worked best.  Thanks to everyone for their efforts.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

688 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