Label fields in reports - display two fields in one label?

[stualk]

Hi Folks,

Is it possible to display the contents of two fields in one label of a report? I have a database that contains names, addresses etc. A report can be produced which is in the form of a letter. Because I use the fields 'firstname' and 'surname', when I get to the first line of the address, the print out looks like this (for example)

Mr Bob Smith

Instead of being together. This is because they are two separate fields in two separate labels. How can I get round this?

Many Thanks

 

[mkov]

Try this:

=[Field1] & " " & [Field2]

The " " will but a space between the two fields.

 

[batteam]

That should do it, but if it doesn't, do you have spaces in your field columns before and/or after the data? If so, you will have to use the 'Trim' functions on your fields to get rid of the spaces, either in your recordsource query or on the report itself.

 

[stualk]

I have tried this but it hasn't quite worked. It's actually a text box that I'm using to display the data in the form. The form is displaying the message "#Error" in the place where the name should be.

The code I used was:

=[First_Name] & " " & [Surname]

...as the control source for the text box. Is there anything else I need to do to make it work?

Many thanks
Stu

 

[batteam]

I assume you're using a bound form. Questions to ask: is this the only text box with the 'Error' on your form? Are other text boxes correctly displaying their info? I could not duplicate your problem during a little testing and messing around with properties. But using two text fields concatenated together as a text box control source on a bound(?)form is a very simple Access process and you should not be encountering any problems in this. Something very basic to either your table, the table columns or your form or the text box itself is wrong. Recheck your table data, try another form, delete that text box and try a new one, try putting other fields from the same table in a text box, etc., etc. If you experiment with some things and try some different configurations you should be able to get this to work.
Good Luck.

 

[mkov]

I agree with batteam. I could not recreat the error either, I use the exact same function to concatinate names in all of my databases and have never come accross the error that you are having. There has to be an underlying problem that is causing the #Error. The First_Name and Surname have to be part or the record source for the form, or you will not be able to do what I said.

 

[Chavito21]

I am having the same ptoblem I am trying to concatinate City, State and ZIP. and I am getting #error.
=Trim([City] & ", " & [State]& " " & [Zip])
I get the #error on all the records?

 

[batteam]

Do this.

Go create a brand new table with only two columns: first_name and last_name. Save your table and name it, then fill in a few rows with some names. Then create a brand new form, bind it to that new table, put a text box in the form and as the control source for it (in the property box), enter this: =[first_name]& " " & [last_name].

Close the form, save it, then open it. If you don't see your first entry from your table (that you just created), then you some other more serious problem with something. Is there anything else wrong with that db - other symptoms that you're not observing yet? Something's wrong somewhere.

Good Luck.

 

[Chavito21]

Okay, Does it matter if the data comes from a query?
I created a table and a report and it works great when the report pulls the data from the table.

 

[mkov]

If a query is your record source, make sure that the fields that you are trying to concatinate are added to your query. If they are not, that means that they are not part of the record source and that could be causing your problem.