Problems with carriage return in address field

B

BillA

I've read other feeds on this subject and I'm unable to fix my problem.

I've developed a query with a field that concatenates multiple fields,
excludes empty strings with Null and introduces carriage returns.
Contatenation and carriage returns work, but my Null doesn't (I want all
fields to move up if a field is empty).

This is what I have so far:

Address: ([tbl_Investigator]![Position] & Chr(13)+Chr(10) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),Null,(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),Null,Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

I would also like to introduce a null for the very first field (Position),
so if the string is empty all the other fields would move up.

Any advice would be greatly be appreciated.
 
K

KARL DEWEY

I assume you are talking about a report. Set the Can Grow & Can Shrink
peoperties to Yes.
 
J

John Spencer

Try the following.

Address: ([tbl_Investigator]![Position] + Chr(13)+Chr(10)) &
([tbl_Investigator]![Address] + Chr(13) + Chr(10)) &
([tbl_Investigator]![Address2] + Chr(13) + Chr(10)) &
([tbl_Investigator]![City] + ", ") & ([tbl_Investigator]![State] + " ")
& [tbl_Investigator]![Zip]

Or try
Address: [tbl_Investigator]![Position]
& IIF(tbl_Investigator.Position is Null,"", Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),"",(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

Now you are testing for null. If the field happens to contain a
zero-length string you will get the line feed.

You can change the test to something like the following to check for
either null or a zero length string.

IIF(LEN(tbl_investigator & "") = 0,"",Chr(13) & Chr(10))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
B

BillA

Hello John:

Thank you for your response. I tried both and found using 'LEN' to test the
string came close, however I received many "#ERROR" returns. Many of the
Error returns were from records that previously returned correctly using my
original expression.

//This expression works on for many records, but returns "#ERROR" for many.
Address: [tbl_Investigator]![Position] &
IIf(Len([tbl_Investigator]![Position]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] & IIf(Len([tbl_Investigator]![Address]) &
""=0,"",Chr(13) & Chr(10)) & [tbl_Investigator]![Address2] &
IIf(Len([tbl_Investigator]![Address2]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]

I belive the problem is with fields that are in fact empty (without string
lengths) which are now returning as #ERROR's using the LEN.

Is there a way of 'cleaning-up' fields (maybe for the entire database) that
appear empty, yet still contain a zero length string.

Thanks again for your time and assistance.
Bill

John Spencer said:
Try the following.

Address: ([tbl_Investigator]![Position] + Chr(13)+Chr(10)) &
([tbl_Investigator]![Address] + Chr(13) + Chr(10)) &
([tbl_Investigator]![Address2] + Chr(13) + Chr(10)) &
([tbl_Investigator]![City] + ", ") & ([tbl_Investigator]![State] + " ")
& [tbl_Investigator]![Zip]

Or try
Address: [tbl_Investigator]![Position]
& IIF(tbl_Investigator.Position is Null,"", Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),"",(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

Now you are testing for null. If the field happens to contain a
zero-length string you will get the line feed.

You can change the test to something like the following to check for
either null or a zero length string.

IIF(LEN(tbl_investigator & "") = 0,"",Chr(13) & Chr(10))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've read other feeds on this subject and I'm unable to fix my problem.

I've developed a query with a field that concatenates multiple fields,
excludes empty strings with Null and introduces carriage returns.
Contatenation and carriage returns work, but my Null doesn't (I want all
fields to move up if a field is empty).

This is what I have so far:

Address: ([tbl_Investigator]![Position] & Chr(13)+Chr(10) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),Null,(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),Null,Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

I would also like to introduce a null for the very first field (Position),
so if the string is empty all the other fields would move up.

Any advice would be greatly be appreciated.
Click to expand...
Click to expand...
 
J

John Spencer

You need to concatenate the empty string BEFORE you test the length.
You were adding it after you tested the length. Simply move the closing
parens so it is right before the equals sign.

Address: [tbl_Investigator]![Position] &
IIf(Len([tbl_Investigator]![Position] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(Len([tbl_Investigator]![Address] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(Len([tbl_Investigator]![Address2] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello John:

Thank you for your response. I tried both and found using 'LEN' to test the
string came close, however I received many "#ERROR" returns. Many of the
Error returns were from records that previously returned correctly using my
original expression.

//This expression works on for many records, but returns "#ERROR" for many.
Address: [tbl_Investigator]![Position] &
IIf(Len([tbl_Investigator]![Position]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] & IIf(Len([tbl_Investigator]![Address]) &
""=0,"",Chr(13) & Chr(10)) & [tbl_Investigator]![Address2] &
IIf(Len([tbl_Investigator]![Address2]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]

I belive the problem is with fields that are in fact empty (without string
lengths) which are now returning as #ERROR's using the LEN.

Is there a way of 'cleaning-up' fields (maybe for the entire database) that
appear empty, yet still contain a zero length string.

Thanks again for your time and assistance.
Bill

John Spencer said:
Try the following.

Address: ([tbl_Investigator]![Position] + Chr(13)+Chr(10)) &
([tbl_Investigator]![Address] + Chr(13) + Chr(10)) &
([tbl_Investigator]![Address2] + Chr(13) + Chr(10)) &
([tbl_Investigator]![City] + ", ") & ([tbl_Investigator]![State] + " ")
& [tbl_Investigator]![Zip]

Or try
Address: [tbl_Investigator]![Position]
& IIF(tbl_Investigator.Position is Null,"", Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),"",(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

Now you are testing for null. If the field happens to contain a
zero-length string you will get the line feed.

You can change the test to something like the following to check for
either null or a zero length string.

IIF(LEN(tbl_investigator & "") = 0,"",Chr(13) & Chr(10))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've read other feeds on this subject and I'm unable to fix my problem.

I've developed a query with a field that concatenates multiple fields,
excludes empty strings with Null and introduces carriage returns.
Contatenation and carriage returns work, but my Null doesn't (I want all
fields to move up if a field is empty).

This is what I have so far:

Address: ([tbl_Investigator]![Position] & Chr(13)+Chr(10) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),Null,(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),Null,Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

I would also like to introduce a null for the very first field (Position),
so if the string is empty all the other fields would move up.

Any advice would be greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
B

BillA

John,

Brilliant !!!
Your assistance in making this work opens a many possibilities in how I can
use my data with other applications.

Thank you again for your time and assistance.
Bill

John Spencer said:
You need to concatenate the empty string BEFORE you test the length.
You were adding it after you tested the length. Simply move the closing
parens so it is right before the equals sign.

Address: [tbl_Investigator]![Position] &
IIf(Len([tbl_Investigator]![Position] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(Len([tbl_Investigator]![Address] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(Len([tbl_Investigator]![Address2] & "")=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello John:

Thank you for your response. I tried both and found using 'LEN' to test the
string came close, however I received many "#ERROR" returns. Many of the
Error returns were from records that previously returned correctly using my
original expression.

//This expression works on for many records, but returns "#ERROR" for many.
Address: [tbl_Investigator]![Position] &
IIf(Len([tbl_Investigator]![Position]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] & IIf(Len([tbl_Investigator]![Address]) &
""=0,"",Chr(13) & Chr(10)) & [tbl_Investigator]![Address2] &
IIf(Len([tbl_Investigator]![Address2]) & ""=0,"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]

I belive the problem is with fields that are in fact empty (without string
lengths) which are now returning as #ERROR's using the LEN.

Is there a way of 'cleaning-up' fields (maybe for the entire database) that
appear empty, yet still contain a zero length string.

Thanks again for your time and assistance.
Bill

John Spencer said:
Try the following.

Address: ([tbl_Investigator]![Position] + Chr(13)+Chr(10)) &
([tbl_Investigator]![Address] + Chr(13) + Chr(10)) &
([tbl_Investigator]![Address2] + Chr(13) + Chr(10)) &
([tbl_Investigator]![City] + ", ") & ([tbl_Investigator]![State] + " ")
& [tbl_Investigator]![Zip]

Or try
Address: [tbl_Investigator]![Position]
& IIF(tbl_Investigator.Position is Null,"", Chr(13) & Chr(10)) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),"",(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),"",Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

Now you are testing for null. If the field happens to contain a
zero-length string you will get the line feed.

You can change the test to something like the following to check for
either null or a zero length string.

IIF(LEN(tbl_investigator & "") = 0,"",Chr(13) & Chr(10))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


BillA wrote:
I've read other feeds on this subject and I'm unable to fix my problem.

I've developed a query with a field that concatenates multiple fields,
excludes empty strings with Null and introduces carriage returns.
Contatenation and carriage returns work, but my Null doesn't (I want all
fields to move up if a field is empty).

This is what I have so far:

Address: ([tbl_Investigator]![Position] & Chr(13)+Chr(10) &
[tbl_Investigator]![Address] &
IIf(IsNull([tbl_Investigator]![Address]),Null,(Chr(13)+Chr(10)) &
[tbl_Investigator]![Address2] &
IIf(IsNull([tbl_Investigator]![Address2]),Null,Chr(13) & Chr(10)) &
[tbl_Investigator]![City] & ", " & [tbl_Investigator]![State] & " " &
[tbl_Investigator]![Zip]))

I would also like to introduce a null for the very first field (Position),
so if the string is empty all the other fields would move up.

Any advice would be greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split data in field on character return 0
Why am I still getting data??? 3
Address box with IIf Statement 7
Sorting a concatenated query problem 7
Replace Carriage Return Symbol 6
Removing Empty Line 5
Populating textbox with more than one row of text 1
Carriage Return in calculated field 7

Top