Concatenate on a report

  • Thread starter Thread starter Chris Freeman
  • Start date Start date
C

Chris Freeman

Is there a way to concatenate fields on a report? I wanted to create a single
City, State, Zip field. I saw marshall Burton's response from 2008:

Try:
FullName: Trim([dbo_Pr_EmpDemo_T]![chrFirstName]) & " " &
Trim([dbo_Pr_EmpDemo_T]![chrLastName])

But when I tried this I get an error: Address3:
trim([tbl_Check_Reissue]![PayeeCity]) & ", " &
trim([tbl_Check_Reissue]![PayeeState])

Is it possible to do this on the report itself, or do I need to do this in a
query first?

TIA
 
Chris

"I get an error" ... doesn't give us much to go on for helping you diagnose
what's happening...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Chris,
The concatenation/calculation can be done in either the query or
the report.
I prefer to do my calcs in the query, because those calc fields are
then "bound", and very easy to total/avg/max... etc... on the report. But I
don't
think there is a iron clad rule either way.

You wrote...
But when I tried this I get an error: Address3:
trim([tbl_Check_Reissue]![PayeeCity]) & ", " &
trim([tbl_Check_Reissue]![PayeeState])
Click to expand...
What is the error?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Chris Freeman said:
Is there a way to concatenate fields on a report? I wanted to create a
single
City, State, Zip field. I saw marshall Burton's response from 2008:

Try:
FullName: Trim([dbo_Pr_EmpDemo_T]![chrFirstName]) & " " &
Trim([dbo_Pr_EmpDemo_T]![chrLastName])


Is it possible to do this on the report itself, or do I need to do this in
a
query first?

TIA
Click to expand...
 
Jeff,
The first try: =[PayeeCity] & ", " & [PayeeState] simply displayed an "
#error" on the report.

Then I saw marshall's reponse and tried: Address3:
trim([tbl_Check_Reissue]![PayeeCity]) & ", " &
trim([tbl_Check_Reissue]![PayeeState])
Click to expand...

And now the form won't open, with the runtime error 3018: could not find "|"
field
--
Chris Freeman
IT Project Coordinator


Jeff Boyce said:
Chris

"I get an error" ... doesn't give us much to go on for helping you diagnose
what's happening...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chris Freeman said:
Is there a way to concatenate fields on a report? I wanted to create a
single
City, State, Zip field. I saw marshall Burton's response from 2008:

Try:
FullName: Trim([dbo_Pr_EmpDemo_T]![chrFirstName]) & " " &
Trim([dbo_Pr_EmpDemo_T]![chrLastName])

But when I tried this I get an error: Address3:
trim([tbl_Check_Reissue]![PayeeCity]) & ", " &
trim([tbl_Check_Reissue]![PayeeState])

Is it possible to do this on the report itself, or do I need to do this in
a
query first?

TIA
Click to expand...
Click to expand...
 
Is there a way to concatenate fields on a report? I wanted to create a single
City, State, Zip field. I saw marshall Burton's response from 2008:

Try:
FullName: Trim([dbo_Pr_EmpDemo_T]![chrFirstName]) & " " &
Trim([dbo_Pr_EmpDemo_T]![chrLastName])

But when I tried this I get an error:  Address3:
trim([tbl_Check_Reissue]![PayeeCity]) & ", " &
trim([tbl_Check_Reissue]![PayeeState])

Is it possible to do this on the report itself, or do I need to do this in a
query first?

TIA
Click to expand...

Yes, you can do this directly in the report. You don´t need to mention
the data source, since there is only one anyway. Try this:

FullName: Trim([chrFirstName]) & " " & Trim([chrLastName])

Note: You can only use fields that are existent in your datasource
(table or query).

Note2: Access sometimes has this weird characteristic of changing
field names automatically. Make sure the field where you put the
string I just posted is NOT named chrFirstName NOR chrLastName. Note
that if it DOES, and you change the name manually, it will also change
the string. Then just open the report, confirm the error message, and
modify the string again. (I know this is not a solution, but the best
workaround I have found so far).

(Note3: The exlamation marks you used initally are wrong anyway. To
fully indicate a field name in a table, the "." is used
(tablename.fieldname). This would apply for you if you would do the
concatenation directly in the query. You don´t need it in the report.)

Hope this helps.
 
OK, I don't know what happened here. I closed the app, and whenh I opened and
ran the report, I got a query data entry box, asking for a value for
Tbl_Check_Reissue. Well, this was wrong, so I removed the table reference in
the formula, and now it works: =[PayeeCity] & ", " & [PayeeState] & " " &
[PayeeZip]

I don't know why didn't work the first time, but whatever, I'm sure I did
something wrong.

Thanks all
 
Sorry, had made a small mistake.

If you do the concatenation in the QUERY, then this would be the
strings:

FullName: Trim([chrFirstName]) & " " & Trim([chrLastName])
Address3: Trim([PayeeCity]) & ", " & trim([PayeeState])

"FullName" and "Address3" are the labels, i. e. the column names in
the query. You would only need to indicate the table names if you use
at least two tables which both contain the field names chrFirstName,
chrLastName etc.

But if you do the concatenation in a calculated field in a report, you
do NOT need a label. The strings you would put in "Control Source" are
these:

= Trim([chrFirstName]) & " " & Trim([chrLastName])
= Address3: Trim([PayeeCity]) & ", " & trim([PayeeState])

Make sure you include the "=".

Sorry for the confusion.
 
Back
Top