Joining two+ field values in one text box

[aexley]

I'm sure this has been answered before so you'll have to forgive me if I'm repeating a question but here it is anyway.

I have a text box that needs to be populated with the contents of two text fields in a query. Using help I came up with the following solution:

=[All Sites].[Prefix] & " " & [All Sites].[Site Contact]

This expression was entered in the ControlSource property of the text box. Trouble is all I'm getting is the #Name? error. This error comes up even if I only try one of the fields and if I use "!" instead of "." This seems a straightforward expression, what am I missing?

Thanks in Advance

aexley

 

[nberryman]

The syntax looks OK but #Name indicates that the feilds are not available.

If you are using a form switch to design view and Click View, Field list. If the fields that you are trying to join are not there then you will need to add them to the underlying query



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[aexley]

Checked as you suggested both fields are in the list. As [All Sites] is the source for my form I tried removing it from the text box controlsource so that it read:

=[Prefix] & " " & [Site Contact]

But this only gave me an error instead. Any more thoughts on this?

Thanks

aexley

 

[JoeMiller]

Make sure that the name of the control with the calculation in it is not named "Prefix" or "Site Contact", that would produce the #Name error. The #Error indicates that either there's a problem with the formula itself or pieces needed to complete the formula (ie: the controls) aren't available.

HTH Joe Miller
joe.miller@flotech.net

 

[aexley]

Ok, checked the name of the control and it was 'Site Contact' so I've changed it to 'On Site Contact' but I'm still getting the #Name? error. As for the pieces of the formula itself they are direct from the underlying query and not controls.

Just an extra question - would "!" (e.g. [All Sites]![Prefix]), be much different to using "." ([All Sites].[Prefix]) in the expression or is it just a convention?

thanks

aexley

 

[JoeMiller]

You'd be better off making this connection in the query supplying the data to the form. In the query make your concatenation and then show that field on the form. If the form is bound to the table, providing the pieces you should get rid of the All Sites and just use the field name to show the data.

HTH Joe Miller
joe.miller@flotech.net

 

[nberryman]

A wild guess . There is a space in your field name, it's possible that you may have 2 spaces in the field name and 1 in the text box.

Try the expression builder. It is the button with a magic wand on it. This allows you to navigate through the tables, queries, forms and reports and by double clicking the control it pastes the name into the text box.

Golden Rule and my tip of the week......... Never have spaces in your field names either

St_Surname

or

StSurname

Not

St Surname Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[aexley]

Hi thanks for all your help everybody, I've joined the fields (in the query)and it works a treat...except that I need the control to be updateable and with the expression it isn't. I realise this is going to be a more complex solution. Could I use an event to determine when someone is trying to change the data and maybe open a little form that is directly linked to the query fields? That would bypass the expression. It's a little clunky but could it work?

thanks

aexley

 

[JoeMiller]

why not put the two fields on the screen instead of combining them? The code to required to allow what you're talking about is rather complex and the amount of oddities in data entry you'd have to account for would be tremendous. Joe Miller
joe.miller@flotech.net

 

[aexley]

That is what I have been doing.

How this started was that I have a search facility that quickly hunts down the appropriate contact but because my contacts were Mr A, Mrs B, Mr C the search wasn't very effective so I created a seperate 'Prefix' field to allow the searching to be conducted on just the actual names. I still needed to show the prefix on-screen and it looked untidy in two seperate text boxes. Maybe I'm paying the price for wanting too many 'bells & whistles'.

Thanks for your help.

aexley

 

[JoeMiller]

You can include a third field that shows the combined names prefix + name and then the users can search/view that box to see the results of their typing... Don't know if that helps but try it out.

Joe Miller
joe.miller@flotech.net

 

[jebry]

Hi aexley!

Just add a button to edit the name which will pop up a form with each part of the name in separate text boxes. Add Me.Requery to the end of the button's code so the user will see the new data.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[aexley]

Thanks Jeff, I'll try it.

Many thanks to you all for your help.

aexley