Combining several fields in Report

L

Lana

I want to combine Street & City in my report, but I keep getting Errors
instead of Data displayed.

What could be wrong? I tryed 2 ways already:

=[BusinessStreet] & ", " & [BusinessCity]

=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") & [BusinessCity]

but still nothing works. (though it work all right when data displayed
separately in 2 boxes).

I made the report through wizard and I have 1 grouping level (by company).
Can somebody advise me what am I doing wrong here?

Thank you.
Lana
 
A

Al Camp

Lana.
I don't see anything wrong with...
=[BusinessStreet] & ", " & [BusinessCity]
Click to expand...

But the second concatenation has a synatax problem... and, doesn't seem
to make sense
=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") & [BusinessCity]
Click to expand...

Should be...
=IIf(IsNull([BusinessStreet]),[BusinessCity],[BusinessStreet] & ", " &
[BusinessCity])
That way, if BusinessStreet is Null, you'll still get just
BusinessCity... If not... you'll get both values.

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Lana said:
I want to combine Street & City in my report, but I keep getting Errors
instead of Data displayed.

What could be wrong? I tryed 2 ways already:

=[BusinessStreet] & ", " & [BusinessCity]

=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") & [BusinessCity]

but still nothing works. (though it work all right when data displayed
separately in 2 boxes).

I made the report through wizard and I have 1 grouping level (by company).
Can somebody advise me what am I doing wrong here?

Thank you.
Lana
Click to expand...
 
A

Allen Browne

Try changing the Name property of the text box.

Access gets confused if the control has the same Name as a field in the
report's RecordSource, but is bound to something else.

Your first example should work:
=[BusinessStreet] & ", " & [BusinessCity]

Another tip: use a mix of & and + for concatenation, e.g.:
=[BusinessStreet] + ", " & [BusinessCity]
That will suppress the comma if BusinessStreet is Null, because:
"A" + Null => Null
whereas:
"A" & Null => "A"
 
L

Lana

Thank you Allen!

When I changed the names of the boxes - everything worked just fine!!!

and thank you for the tip on using +/& - it helped me great!
i would be lost in syntaxis doing IIf(IsNull... for combining 3-4 fields
which I intended to do.

Lana


Allen Browne said:
Try changing the Name property of the text box.

Access gets confused if the control has the same Name as a field in the
report's RecordSource, but is bound to something else.

Your first example should work:
=[BusinessStreet] & ", " & [BusinessCity]

Another tip: use a mix of & and + for concatenation, e.g.:
=[BusinessStreet] + ", " & [BusinessCity]
That will suppress the comma if BusinessStreet is Null, because:
"A" + Null => Null
whereas:
"A" & Null => "A"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lana said:
I want to combine Street & City in my report, but I keep getting Errors
instead of Data displayed.

What could be wrong? I tryed 2 ways already:

=[BusinessStreet] & ", " & [BusinessCity]

=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") & [BusinessCity]

but still nothing works. (though it work all right when data displayed
separately in 2 boxes).

I made the report through wizard and I have 1 grouping level (by company).
Can somebody advise me what am I doing wrong here?

Thank you.
Lana
Click to expand...
Click to expand...
 
A

Al Camp

Lana,
As usual, Allen hit it right on the head... AND upon revisiting your 2
statements, neither one of them is wrong!
Mea Culpa
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Lana said:
Thank you Allen!

When I changed the names of the boxes - everything worked just fine!!!

and thank you for the tip on using +/& - it helped me great!
i would be lost in syntaxis doing IIf(IsNull... for combining 3-4 fields
which I intended to do.

Lana


Allen Browne said:
Try changing the Name property of the text box.

Access gets confused if the control has the same Name as a field in the
report's RecordSource, but is bound to something else.

Your first example should work:
=[BusinessStreet] & ", " & [BusinessCity]

Another tip: use a mix of & and + for concatenation, e.g.:
=[BusinessStreet] + ", " & [BusinessCity]
That will suppress the comma if BusinessStreet is Null, because:
"A" + Null => Null
whereas:
"A" & Null => "A"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lana said:
I want to combine Street & City in my report, but I keep getting Errors
instead of Data displayed.

What could be wrong? I tryed 2 ways already:

=[BusinessStreet] & ", " & [BusinessCity]

=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") &
[BusinessCity]

but still nothing works. (though it work all right when data displayed
separately in 2 boxes).

I made the report through wizard and I have 1 grouping level (by
company).
Can somebody advise me what am I doing wrong here?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
L

Lana

Thank you Al Camp!
Lana


Al Camp said:
Lana,
As usual, Allen hit it right on the head... AND upon revisiting your 2
statements, neither one of them is wrong!
Mea Culpa
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Lana said:
Thank you Allen!

When I changed the names of the boxes - everything worked just fine!!!

and thank you for the tip on using +/& - it helped me great!
i would be lost in syntaxis doing IIf(IsNull... for combining 3-4 fields
which I intended to do.

Lana


Allen Browne said:
Try changing the Name property of the text box.

Access gets confused if the control has the same Name as a field in the
report's RecordSource, but is bound to something else.

Your first example should work:
=[BusinessStreet] & ", " & [BusinessCity]

Another tip: use a mix of & and + for concatenation, e.g.:
=[BusinessStreet] + ", " & [BusinessCity]
That will suppress the comma if BusinessStreet is Null, because:
"A" + Null => Null
whereas:
"A" & Null => "A"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to combine Street & City in my report, but I keep getting Errors
instead of Data displayed.

What could be wrong? I tryed 2 ways already:

=[BusinessStreet] & ", " & [BusinessCity]

=IIf(IsNull([BusinessStreet]),"",[BusinessStreet] & ", ") &
[BusinessCity]

but still nothing works. (though it work all right when data displayed
separately in 2 boxes).

I made the report through wizard and I have 1 grouping level (by
company).
Can somebody advise me what am I doing wrong here?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Top