Eliminating Blank Address Lines

[Paul Richards]

I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[strive4peace]

Hi Paul,

make the height of each control containing the fields very
short -- like 0.02, set CanGrow--> yes, and close up the
vertical space between your controls

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[John Spencer]

You could use a concatenated calculated field

Assumption:
Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) &
(Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you will
need to adjust the formula. You can do that by moving the Chr(13) + Chr(10) to
the beginning of each address line string and removing the Chr(13) Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I put
each line of data onto a separate line.

 

[strive4peace]

Hi John,

great explanation!

Is it more efficient to concatenate in the query and have
one control instead of more on the report?

Would Access have a speed issue when the concatenated string
got long?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You could use a concatenated calculated field

Assumption:
Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) &
(Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you will
need to adjust the formula. You can do that by moving the Chr(13) + Chr(10) to
the beginning of each address line string and removing the Chr(13) Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I put
each line of data onto a separate line.

I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[John Spencer]

Well if you need to order by one of the fields then you have to include it again
in the query so it will be available in the report's sorting and grouping.

I don't know that there is much perceived difference in doing it in a query
versus doing it in a report's control. I often combine the two. That is if I
want to sort the report by the postal code or the city, then I 'll build the
street address without the city, state, and postal code in query. I can then
sort by the city (or postal code) in the report.

Hi John,

great explanation!

Is it more efficient to concatenate in the query and have
one control instead of more on the report?

Would Access have a speed issue when the concatenated string
got long?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You could use a concatenated calculated field

Assumption:
Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) &
(Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you will
need to adjust the formula. You can do that by moving the Chr(13) + Chr(10) to
the beginning of each address line string and removing the Chr(13) Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I put
each line of data onto a separate line.

I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[Paul Richards]

Thanks for that.

Should this be

IIF(IsNull(Address_Line 2),Null, Address_Line2 & Chr(13) & Chr(10)) & ……

Why do I get little boxes appearing between the fields I am concatenating?

You could use a concatenated calculated field

Assumption:
Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) &
(Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you will
need to adjust the formula. You can do that by moving the Chr(13) + Chr(10) to
the beginning of each address line string and removing the Chr(13) Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I put
each line of data onto a separate line.

I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[John Spencer]

You can use either construct in a query. IsNull() or SomeField Is Null. In
VBA you can only use IsNull().

If you are getting little boxes, that indicates that you don't have both
Chr(13) and Chr(10) in the string, but only only one or the other or perhaps
you've included a space between the two or there is some other problem.

Thanks for that.

Should this be

IIF(IsNull(Address_Line 2),Null, Address_Line2 & Chr(13) & Chr(10)) & ……

Why do I get little boxes appearing between the fields I am concatenating?

You could use a concatenated calculated field

Assumption: Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) & IIF(Address_Line2
is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles
concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls
as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) & (Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you
will
need to adjust the formula. You can do that by moving the Chr(13) +
Chr(10) to
the beginning of each address line string and removing the Chr(13)
Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I
put
each line of data onto a separate line.

I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[Paul Richards]

John: thanks - I'll have another look at what I've done. Is there
anywhere in Access help I can get a list of ASCII characters?

You can use either construct in a query. IsNull() or SomeField Is Null. In
VBA you can only use IsNull().

If you are getting little boxes, that indicates that you don't have both
Chr(13) and Chr(10) in the string, but only only one or the other or perhaps
you've included a space between the two or there is some other problem.

Thanks for that.

Should this be

IIF(IsNull(Address_Line 2),Null, Address_Line2 & Chr(13) & Chr(10)) & ……

Why do I get little boxes appearing between the fields I am concatenating?

You could use a concatenated calculated field

Assumption: Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) & IIF(Address_Line2
is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles
concatenation
using + and &. The "&" operator treats nulls as if they were zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls
as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) & (Address_line2 + Chr(13) + Chr(10)) &
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you
will
need to adjust the formula. You can do that by moving the Chr(13) +
Chr(10) to
the beginning of each address line string and removing the Chr(13)
Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I
put
each line of data onto a separate line.

Paul Richards wrote:


I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two blank
lines when printing the invoice?

Thanks

 

[John Spencer]

Search for "Character Set" in VBA help.

It can be tough to find so I hope you will excuse the detailed steps.
-- Open a code module
-- Press F1 key
-- Click in the Answer Wizard
-- Type in "Character Set"
-- Press the search button

You should get two hits for charts (one for 1-127 and second for 128 to 255)

John: thanks - I'll have another look at what I've done. Is there anywhere
in Access help I can get a list of ASCII characters?

You can use either construct in a query. IsNull() or SomeField Is Null.
In VBA you can only use IsNull().

If you are getting little boxes, that indicates that you don't have both
Chr(13) and Chr(10) in the string, but only only one or the other or
perhaps you've included a space between the two or there is some other
problem.

Thanks for that.

Should this be

IIF(IsNull(Address_Line 2),Null, Address_Line2 & Chr(13) & Chr(10)) & ……

Why do I get little boxes appearing between the fields I am
concatenating?



John Spencer wrote:

You could use a concatenated calculated field

Assumption: Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles
concatenation
using + and &. The "&" operator treats nulls as if they were
zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls
as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) & (Address_line2 + Chr(13) + Chr(10))
&
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you
will
need to adjust the formula. You can do that by moving the Chr(13) +
Chr(10) to
the beginning of each address line string and removing the Chr(13)
Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I
put
each line of data onto a separate line.

Paul Richards wrote:


I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two
blank
lines when printing the invoice?

Thanks

 

[Paul Richards]

John: thanks

Search for "Character Set" in VBA help.

It can be tough to find so I hope you will excuse the detailed steps.
-- Open a code module
-- Press F1 key
-- Click in the Answer Wizard
-- Type in "Character Set"
-- Press the search button

You should get two hits for charts (one for 1-127 and second for 128 to 255)

John: thanks - I'll have another look at what I've done. Is there anywhere
in Access help I can get a list of ASCII characters?

You can use either construct in a query. IsNull() or SomeField Is Null.
In VBA you can only use IsNull().

If you are getting little boxes, that indicates that you don't have both
Chr(13) and Chr(10) in the string, but only only one or the other or
perhaps you've included a space between the two or there is some other
problem.




Thanks for that.

Should this be

IIF(IsNull(Address_Line 2),Null, Address_Line2 & Chr(13) & Chr(10)) & ……

Why do I get little boxes appearing between the fields I am
concatenating?



John Spencer wrote:


You could use a concatenated calculated field

Assumption: Address_Line1 always has a value
All address lines are null if they are blank (no zero length strings)

One Method to do that is as follows

Field: FullAddress: Address_Line1 & Chr(13) & Chr(10) &
IIF(Address_Line2 is Null,Null,Address_line2 & Chr(13) & Chr(10)) &
IIF(Address_Line3 is Null,Null,Address_line3 & Chr(13) & Chr(10)) &
IIF(Address_Line4 is Null,Null,Address_line4 & Chr(13) & Chr(10)) &
[Post/zip Code]

You can also take advantage of the different way Access handles
concatenation
using + and &. The "&" operator treats nulls as if they were
zero-length
strings so it doesn't propagate the null. The "+" operator treats nulls
as
nulls and Null plus anything is Null.

Address_Line1 & Chr(13) & Chr(10) & (Address_line2 + Chr(13) + Chr(10))
&
(Address_line3 + Chr(13) + Chr(10)) &
(Address_line4 + Chr(13) + Chr(10)) &
[Post/zip Code]

If you want Post/Zip Code on the same line as the last Address line you
will
need to adjust the formula. You can do that by moving the Chr(13) +
Chr(10) to
the beginning of each address line string and removing the Chr(13)
Chr(10)
combination from the first address line.

By the way all the above are on one line in the query, but for clarity I
put
each line of data onto a separate line.

Paul Richards wrote:



I have a query from which an invoice is printed. How do I eliminate
blank address lines?

For example if I have address_line1, address_line2,
address_line3,address_line4, post/zip code and there is data only in
address_lines 1 and 2 and post/zip/code,how do I eliminate the two
blank
lines when printing the invoice?

Thanks