I have a report where I print out the client's address. Currently I can't connect the three different fields for printing purposes. For example if you have New York, NY 10001. On my report they are three seperate fields and it looks like New York , NY 10001. I'd like to just concat them together for display purposes. I did something like this with the user's first name last name. [Fname]&" "&[Lname]. I tried this with the city and state and it would not work. Kept getting an error message. Please help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to combine City, State and Zip Fields in Report? 3
- Thread starter lasmith05
- Start date
lasmith105
In an unbound text box, you should be able to combine the 3 fields.
=[City] & ", " & [State] & " " & [Zip]
Do you have extra spaces in one of your fields, try using the Trim function in front of the field.
Tom
In an unbound text box, you should be able to combine the 3 fields.
=[City] & ", " & [State] & " " & [Zip]
Do you have extra spaces in one of your fields, try using the Trim function in front of the field.
Tom
-
2
- #3
lasmith05,
" it would not work. Kept getting an error message" doesn't tell us what the error message is.
Did you set the name of the text box to be something other than a field name?
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
" it would not work. Kept getting an error message" doesn't tell us what the error message is.
Did you set the name of the text box to be something other than a field name?
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #4
When I put =[City] & ", " & [State] & " " & [Zip] in the field's data source. When I view the report, it just shows #Error instead of the data. If I don't concat the fields they show up fine.
I do not use spaces, but in any case I have tried both ways.
I do not use spaces, but in any case I have tried both ways.
-
1
- #5
I think whats happening here is that you only have a single control on the report, and are then attempting to bind the above [blue]=[City] & ", " & [State] & " " & [Zip][/blue] expression as the control source to that control.
For the above to work, you need to create separate bound controls for each on the form; name the controls City, State and Zip and bind them to their respective fields. Set the visible property of each of these controls to False, as they are 'working' controls, and are not for display purposes.
Then create a fourth control, and name it CityStateZip. Set its ControlSource property to the blue expression above.
The reason you get this problem, is that the expression refers to controls on the report and not to fields in the underlying recordsource. So when you dont have the three explicit controls on the report, the expression cannot be evaluated; hense the error.
The other option you have, is to use a query or sql as the reports recordsource, and add the expression to the select part, so that it becomes a field in its own right inasfar as the report is concerned; eg.
SELECT *,
[City] & ", " & [State] & " " & [Zip] AS CityStateZip
FROM tblYourTable
With this method, the distinct City, State and Zip controls do not need to be included separately and hidden on the report.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
For the above to work, you need to create separate bound controls for each on the form; name the controls City, State and Zip and bind them to their respective fields. Set the visible property of each of these controls to False, as they are 'working' controls, and are not for display purposes.
Then create a fourth control, and name it CityStateZip. Set its ControlSource property to the blue expression above.
The reason you get this problem, is that the expression refers to controls on the report and not to fields in the underlying recordsource. So when you dont have the three explicit controls on the report, the expression cannot be evaluated; hense the error.
The other option you have, is to use a query or sql as the reports recordsource, and add the expression to the select part, so that it becomes a field in its own right inasfar as the report is concerned; eg.
SELECT *,
[City] & ", " & [State] & " " & [Zip] AS CityStateZip
FROM tblYourTable
With this method, the distinct City, State and Zip controls do not need to be included separately and hidden on the report.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
You do not need to create separate controls bound to the individual fields. You only need to bind a control to:
=[City] & ", " & [State] & " " & [Zip]
I (and others) do this all the time. There is only one trick/tip.
lasmith05,
You seemed to have ignored my question in my previous post.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
=[City] & ", " & [State] & " " & [Zip]
I (and others) do this all the time. There is only one trick/tip.
lasmith05,
You seemed to have ignored my question in my previous post.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
lasmith05
I agree with dhookom and use that tip/trick all the time. That's why I suggested it - being an unbound field - in my reply above.
But I also recall being frustrated because I couldn't get a concatenated field to work...and it was always because I was trying to do the concatenation on one of the bound fields I had dragged in from the Fields list (that's what produces the #Error), rather than making a new unbound field and then placing the concatenated construction in that field.
Tom
I agree with dhookom and use that tip/trick all the time. That's why I suggested it - being an unbound field - in my reply above.
But I also recall being frustrated because I couldn't get a concatenated field to work...and it was always because I was trying to do the concatenation on one of the bound fields I had dragged in from the Fields list (that's what produces the #Error), rather than making a new unbound field and then placing the concatenated construction in that field.
Tom
Guys (gals?),
The tip/trick is to rename the text box so that it is not also the name of a field. By default, if you drag a field to a form (or report) it is given the name of the field (if there is no control already with that name). When you change the control source as we are suggesting, it's like:
-inviting me (Duane) to a party
-I bring my family of 5 with me to the party (or they crash it)
-still referring to everyone in my family as "Duane" rather than referring to us as "txtDuanesFamily"
Do you see how confusing this might be? I understand that George Foreman named all of his sons George but this is really odd and probably causes lots of confusion. I suppose they all have nicknames like txtGeorge1, txtGeorge2, txtGeorge3,...
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
The tip/trick is to rename the text box so that it is not also the name of a field. By default, if you drag a field to a form (or report) it is given the name of the field (if there is no control already with that name). When you change the control source as we are suggesting, it's like:
-inviting me (Duane) to a party
-I bring my family of 5 with me to the party (or they crash it)
-still referring to everyone in my family as "Duane" rather than referring to us as "txtDuanesFamily"
Do you see how confusing this might be? I understand that George Foreman named all of his sons George but this is really odd and probably causes lots of confusion. I suppose they all have nicknames like txtGeorge1, txtGeorge2, txtGeorge3,...
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #9
*******
lasmith05,
You seemed to have ignored my question in my previous post.
lasmith05,
" it would not work. Kept getting an error message" doesn't tell us what the error message is.
Did you set the name of the text box to be something other than a field name?
Duane
*******
I did say what the error message I received was. #Error when you view the report. Ass for name of the text box, I have tried doing the concatanation in both an unbound box and by just trying modify the current City field and add concated fields to them. I think all of this is strange considering it was so easy with the User Fname + Lname.
lasmith05,
You seemed to have ignored my question in my previous post.
lasmith05,
" it would not work. Kept getting an error message" doesn't tell us what the error message is.
Did you set the name of the text box to be something other than a field name?
Duane
*******
I did say what the error message I received was. #Error when you view the report. Ass for name of the text box, I have tried doing the concatanation in both an unbound box and by just trying modify the current City field and add concated fields to them. I think all of this is strange considering it was so easy with the User Fname + Lname.
- Thread starter
- #10
This may sound like a silly suggestion on my part but add an unbound textbox to your form and do the concatenation of the control source there.
#Error is caused by syntax errors.
Here's the reason for the suggestion it will give you feed back right away.
Try this first, then add the zip code.
=[City]&","&[State]
If that works cut and paste it as the control source for the textbox on your report.
As long as all the control names are the same it should work without any problems.
Good luck,
Steve
#Error is caused by syntax errors.
Here's the reason for the suggestion it will give you feed back right away.
Try this first, then add the zip code.
=[City]&","&[State]
If that works cut and paste it as the control source for the textbox on your report.
As long as all the control names are the same it should work without any problems.
Good luck,
Steve
- Thread starter
- #12
- Thread starter
- #13
Seems to be more to this problem than meets the eye. I have also encountered the problem before and resolved it as I described above.
Agree with Duane that you dont normally need to include all fields separately, so stand corrected on this account, but it obviously does become a (currently unexplained) workaround which resolves the problem - I've had to use it myself before to resolve this error.
Perhaps it has to do with the version of Access that you are using / OS? / Service Pack? At any rate glad it did the trick.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
Agree with Duane that you dont normally need to include all fields separately, so stand corrected on this account, but it obviously does become a (currently unexplained) workaround which resolves the problem - I've had to use it myself before to resolve this error.
Perhaps it has to do with the version of Access that you are using / OS? / Service Pack? At any rate glad it did the trick.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
- Thread starter
- #15
dhookom: Did you set the name of the text box to be something other than a field name?
Well when I set the name to the same as one of the fields, it certainly triggered the #error problem; I would'nt have thought that this would be a problem, but Access seems to 'look at' both the underlying record source field names AND the control names, and gets itself confused if they are the same AND used in a recordsource expression.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
Well when I set the name to the same as one of the fields, it certainly triggered the #error problem; I would'nt have thought that this would be a problem, but Access seems to 'look at' both the underlying record source field names AND the control names, and gets itself confused if they are the same AND used in a recordsource expression.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
- Status
Similar threads
- Locked
- Question
- Locked
- Question