473,386 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Eliminating blank lines in Query and Table records

I have created an address field that consists of Address1; Address2; City, State and Zip. When all are populated this would create a three line record. However, when there is no Address2, I would like it to appear as a two line record. What do I need to insert here? Here is what I have used to date:
Expr: Address1 & Chr(13) & Chr(10) & Address2 & Chr(13) & Chr(10) & City & ', ' & State & ' ' & Zip

In Reports I have used the Trim function, but that does not seem to work in Queries and Tables.

Any suggestions??
May 28 '10 #1

✓ answered by ChipR

You could use something similar to:
Expand|Select|Wrap|Line Numbers
  1. Expr: Address1 & Chr(13) & Chr(10) & 
  2.   IIf(IsNull(Address2),"", Address2 & Chr(13) & Chr(10)) & 
  3.   City & ', ' & State & " " & Zip
If no Address2 means the field is null. If it's an empty string, you would use
Expand|Select|Wrap|Line Numbers
  1.   IIf(Address2="",...

2 1530
ADezii
8,834 Expert 8TB
@Bigdaddrock
Expand|Select|Wrap|Line Numbers
  1. Expr: [Address1] & IIf(IsNull([Address2]),"",Chr(13) & Chr(10) & [Address2]) & Chr(13) & Chr(10) & [City] & ', ' & [State] & ' ' & [Zip]
May 28 '10 #2
ChipR
1,287 Expert 1GB
You could use something similar to:
Expand|Select|Wrap|Line Numbers
  1. Expr: Address1 & Chr(13) & Chr(10) & 
  2.   IIf(IsNull(Address2),"", Address2 & Chr(13) & Chr(10)) & 
  3.   City & ', ' & State & " " & Zip
If no Address2 means the field is null. If it's an empty string, you would use
Expand|Select|Wrap|Line Numbers
  1.   IIf(Address2="",...
May 28 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Ruben | last post by:
Hello. I am trying to read a small text file using the readline statement. I can only read the first 2 records from the file. It stops at the blank lines or at lines with only spaces. I have a...
0
by: Luke Airig | last post by:
I am trying to merge two xml files based on common date/time and then write out a tab-delimited xml file with the header record from one of the input files concatenated in front of the merged...
5
by: MX1 | last post by:
Hi, I have a report with Name1 and Name2 in the address section . Sometimes, Name2 is not populated with data via the query that feeds the report. Unfortunately, the blank line stays in the...
1
by: Melissa | last post by:
I created a report that is grouped and I set it to go to a new page after each section. Each page will have a different number of records. I have a function that looks at the number of records and...
6
by: Melissa | last post by:
Does anyone have a generic procedure for adding blank lines to reports like Sales details, PO details and/or Orders details. The procedure would need to count the number of line items, determine...
1
by: DAnne | last post by:
Hi, I have checked your archives but have not been able to find anything that works for my situation. I have a for loop that brings back a list of unique responses for each section in a report....
6
by: EricR | last post by:
I am trying to bcp import a text file into a SQL Server 2000 database. The text file is coming out of a java application where order information is written to the text file. Each record is on it's...
8
by: Swizylstik | last post by:
I'm a newbie to ASP and databases. I have created a simple contact list database and an asp page that shows the contact listing. So far so good. Here's what I have:...
2
by: kjosh | last post by:
Hi, i have created a script which reads records from an input file and then writes to multiple txt files based on one of the fields (from input file). While writing m getting blank lines between...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.