Errors with text box formulas

[Guest]

Hello, I am attempting to reduce space in a report by entering formulas in
the control source of two unbound text boxes (along with CanShrink set to Yes
for the text box and detail section). I am experiencing problems with both
and can't figure out why..

The first text box is intended to return the customer's address. I have
noticed that where there is a null, I get something that looks like an empty
tickbox appearing after the text of the last field containing data. I have
'Allow zero length' set to No for all the address fields. The formula is as
follows:

=([address1] & (Chr(13) & Chr(10)+[address2]) & (Chr(13) &
Chr(10)+[address3]) & (Chr(13) & Chr(10)+[address4]) & (Chr(13) &
Chr(10)+[postcode]))

In the above, the record I'm testing the report on has no data in address3,
and I get the empty-tickbox style symbol at the end of the text of address2.

The second text box should list a dozen date fields, but, as above, ignore
the fields with no data. I have added just the first two fields and am get
nothing but '#Error' when I run the report. The formula I have so far is:

=("Date received: " & [d_received] & (Chr(13) & Chr(10) & "Date of initial
contact: "+[d_initial_contact]))

By way of experimentation I inserted a text field instead of the
[d_initial_contact] date field and found that it returned the data on two
lines, as expected. Also, the formula returns the [d_received] date no
problem. So the problem appears to be with the subsequent date fields. Does
the '+' not work with dates? Is there a workaround?

Any help would be much appreciated.

Many thanks

Tim Long

 

[Guest]

The + sign is a mathmatical operator
The & sign is a concatenation operator
Since date data types are actually stored as a number, it may be it is
trying to do math rather than the concatenation you are wanting.
It is always better to use & for concatenation. In addition, you could
simplify your code a little by using one of the VBA constants that represent
the CrLf. You can use vbNewLine or vbCrLf. I usually use vbNewLine, because
it is more self documenting.

=[address1] & vbNewLIne & [address2] & vbNewLine & [address3] & vbNewLine &
[address4] & vbNewLine & [postcode]

="Date received: " & [d_received] & vbNewLine & "Date of initial contact:
" & [d_initial_contact]

Additionally, if the value in the missing address field is NULL, then you
can use the Nz function to eliminate the problem if it does not go away when
you replace the + with &

=Nz([address1],"") & vbNewLIne & Nz([address2],"") & vbNewLine &
Nz([address3],"") & vbNewLine & Nz([address4],"") & vbNewLine &
Nz([postcode],"")

 

[Guest]

Many thanks. Not quite there yet unfortunately

The address solution leaves an empty space where there is a null, instead of
populating that line/row with data from the next non-null field and the same
for the date solution. Maybe a solution would be a series of IIfs in code in
the On Print event?

The + sign is a mathmatical operator
The & sign is a concatenation operator
Since date data types are actually stored as a number, it may be it is
trying to do math rather than the concatenation you are wanting.
It is always better to use & for concatenation. In addition, you could
simplify your code a little by using one of the VBA constants that represent
the CrLf. You can use vbNewLine or vbCrLf. I usually use vbNewLine, because
it is more self documenting.

=[address1] & vbNewLIne & [address2] & vbNewLine & [address3] & vbNewLine &
[address4] & vbNewLine & [postcode]

="Date received: " & [d_received] & vbNewLine & "Date of initial contact:
" & [d_initial_contact]

Additionally, if the value in the missing address field is NULL, then you
can use the Nz function to eliminate the problem if it does not go away when
you replace the + with &

=Nz([address1],"") & vbNewLIne & Nz([address2],"") & vbNewLine &
Nz([address3],"") & vbNewLine & Nz([address4],"") & vbNewLine &
Nz([postcode],"")
--
Dave Hargis, Microsoft Access MVP

Hello, I am attempting to reduce space in a report by entering formulas in
the control source of two unbound text boxes (along with CanShrink set to Yes
for the text box and detail section). I am experiencing problems with both
and can't figure out why..

The first text box is intended to return the customer's address. I have
noticed that where there is a null, I get something that looks like an empty
tickbox appearing after the text of the last field containing data. I have
'Allow zero length' set to No for all the address fields. The formula is as
follows:

=([address1] & (Chr(13) & Chr(10)+[address2]) & (Chr(13) &
Chr(10)+[address3]) & (Chr(13) & Chr(10)+[address4]) & (Chr(13) &
Chr(10)+[postcode]))

In the above, the record I'm testing the report on has no data in address3,
and I get the empty-tickbox style symbol at the end of the text of address2.

The second text box should list a dozen date fields, but, as above, ignore
the fields with no data. I have added just the first two fields and am get
nothing but '#Error' when I run the report. The formula I have so far is:

=("Date received: " & [d_received] & (Chr(13) & Chr(10) & "Date of initial
contact: "+[d_initial_contact]))

By way of experimentation I inserted a text field instead of the
[d_initial_contact] date field and found that it returned the data on two
lines, as expected. Also, the formula returns the [d_received] date no
problem. So the problem appears to be with the subsequent date fields. Does
the '+' not work with dates? Is there a workaround?

Any help would be much appreciated.

Many thanks

Tim Long

 

[Guest]

Okay:

=IIf(IsNull([address1]), "", [address1] & vbNewLine) &
IIf(IsNull([address2]), "", [address2] & vbNewLine) &
IIf(IsNull([address3]),"", [address3] & vbNewLine) &
IIf(IsNull([address4],"", [address4] & vbNewLine) & [postcode]
--
Dave Hargis, Microsoft Access MVP

Many thanks. Not quite there yet unfortunately

The address solution leaves an empty space where there is a null, instead of
populating that line/row with data from the next non-null field and the same
for the date solution. Maybe a solution would be a series of IIfs in code in
the On Print event?

The + sign is a mathmatical operator
The & sign is a concatenation operator
Since date data types are actually stored as a number, it may be it is
trying to do math rather than the concatenation you are wanting.
It is always better to use & for concatenation. In addition, you could
simplify your code a little by using one of the VBA constants that represent
the CrLf. You can use vbNewLine or vbCrLf. I usually use vbNewLine, because
it is more self documenting.

=[address1] & vbNewLIne & [address2] & vbNewLine & [address3] & vbNewLine &
[address4] & vbNewLine & [postcode]

="Date received: " & [d_received] & vbNewLine & "Date of initial contact:
" & [d_initial_contact]

Additionally, if the value in the missing address field is NULL, then you
can use the Nz function to eliminate the problem if it does not go away when
you replace the + with &

=Nz([address1],"") & vbNewLIne & Nz([address2],"") & vbNewLine &
Nz([address3],"") & vbNewLine & Nz([address4],"") & vbNewLine &
Nz([postcode],"")
--
Dave Hargis, Microsoft Access MVP

Hello, I am attempting to reduce space in a report by entering formulas in
the control source of two unbound text boxes (along with CanShrink set to Yes
for the text box and detail section). I am experiencing problems with both
and can't figure out why..

The first text box is intended to return the customer's address. I have
noticed that where there is a null, I get something that looks like an empty
tickbox appearing after the text of the last field containing data. I have
'Allow zero length' set to No for all the address fields. The formula is as
follows:

=([address1] & (Chr(13) & Chr(10)+[address2]) & (Chr(13) &
Chr(10)+[address3]) & (Chr(13) & Chr(10)+[address4]) & (Chr(13) &
Chr(10)+[postcode]))

In the above, the record I'm testing the report on has no data in address3,
and I get the empty-tickbox style symbol at the end of the text of address2.

The second text box should list a dozen date fields, but, as above, ignore
the fields with no data. I have added just the first two fields and am get
nothing but '#Error' when I run the report. The formula I have so far is:

=("Date received: " & [d_received] & (Chr(13) & Chr(10) & "Date of initial
contact: "+[d_initial_contact]))

By way of experimentation I inserted a text field instead of the
[d_initial_contact] date field and found that it returned the data on two
lines, as expected. Also, the formula returns the [d_received] date no
problem. So the problem appears to be with the subsequent date fields. Does
the '+' not work with dates? Is there a workaround?

Any help would be much appreciated.

Many thanks

Tim Long

 

[John Spencer]

If you are going to use the + concatenation operator to eliminate the new
line characters you must use it between Chr(13) and Chr(10) also.

=([address1] & (Chr(13) + Chr(10)+[address2]) & (Chr(13) +
Chr(10)+[address3]) & (Chr(13) + Chr(10)+[address4]) &
(Chr(13) + Chr(10)+[postcode]))

Or alternative
=([address1] & ((Chr(13) & Chr(10))+[address2]) & ((Chr(13) &
Chr(10))+[address3]) & ((Chr(13) & Chr(10)) +[address4]) &
((Chr(13) & Chr(10))+[postcode]))

"+" propagates Nulls, while "&" treats nulls as zero-length strings.

If Address2 is Null
Chr(13) + Chr(10)+[address2] returns Null
Chr(13) & Chr(10)+[address2] returns Chr(13)


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