How to eliminate space when field has null value in text string?

[jmuirman]

Some of the fields in my text string will be blank - can I design it such
that the space of that null field won't take up space in my report?

Here's what it looks like - "Guaranteed Lump-Sum - $150,000 payable on
7/1/2015"

Again thanks a million!

John

Once again here is my whole string:

=[annuity type1] & " - " & Format([benefit amount1],"Currency") & " " &
[payable1] & " " & [mode1] & " " & [guaranty1] & " " & [years1] & " " &
[years] & "" & [months1] & " " & [begon] & " " & [date of 1st payment1] & " "
& Format([BEG AGE],"Standard") & " " & [lastpymt] & " " & [date of last
payment1] & " " & Format([ending age],"Standard")

 

[Jeff Boyce]

Your string appears to have all the "blanks" squeezed out -- what fields
were left out?

Take a look at the Nz() function to turn nulls into empty strings ("").

Good luck

Jeff Boyce
<Access MVP>

 

[jmuirman]

I'm not understanding the Nz function - seems like it only applies to numbers
- what happens is in my report an unused field, though it doesn't show, takes
up space so that my text string has a noticeable space between text or
fields on either side...

John isn't very smart in Access

That space is just a field without data - - Make sense?

Thanks again,

John

Your string appears to have all the "blanks" squeezed out -- what fields
were left out?

Take a look at the Nz() function to turn nulls into empty strings ("").

Good luck

Jeff Boyce
<Access MVP>

Some of the fields in my text string will be blank - can I design it such
that the space of that null field won't take up space in my report?

Here's what it looks like - "Guaranteed Lump-Sum - $150,000 payable on
7/1/2015"

Again thanks a million!

John

Once again here is my whole string:

=[annuity type1] & " - " & Format([benefit amount1],"Currency") & " " &
[payable1] & " " & [mode1] & " " & [guaranty1] & " " & [years1] & " " &
[years] & "" & [months1] & " " & [begon] & " " & [date of 1st payment1] &
" "
& Format([BEG AGE],"Standard") & " " & [lastpymt] & " " & [date of last
payment1] & " " & Format([ending age],"Standard")

 

[Jeff Boyce]

So, you're saying you are using one textbox per field.

Another approach would be to build a concatenated field in your query (or
your report), with something like:

CombinedString: Nz([FirstField],"") & " " & Nz([SecondField],"") & ...

NOTE: Nz() applies to any nulls, not just number fields

Good luck

Jeff Boyce
<Access MVP>

I'm not understanding the Nz function - seems like it only applies to
numbers
- what happens is in my report an unused field, though it doesn't show,
takes
up space so that my text string has a noticeable space between text or
fields on either side...

John isn't very smart in Access

That space is just a field without data - - Make sense?

Thanks again,

John

Your string appears to have all the "blanks" squeezed out -- what fields
were left out?

Take a look at the Nz() function to turn nulls into empty strings ("").

Good luck

Jeff Boyce
<Access MVP>

Some of the fields in my text string will be blank - can I design it
such
that the space of that null field won't take up space in my report?

Here's what it looks like - "Guaranteed Lump-Sum - $150,000 payable on
7/1/2015"

Again thanks a million!

John

Once again here is my whole string:

=[annuity type1] & " - " & Format([benefit amount1],"Currency") & " " &
[payable1] & " " & [mode1] & " " & [guaranty1] & " " & [years1] & " " &
[years] & "" & [months1] & " " & [begon] & " " & [date of 1st payment1]
&
" "
& Format([BEG AGE],"Standard") & " " & [lastpymt] & " " & [date of last
payment1] & " " & Format([ending age],"Standard")