Concatenating Fields From Query To Form

[databasedonr]

Greetings:

I am having problems with a form that is based on a query: I want to take three of the fields in the query and concatenate them into one field on the form. I think this is simple, but I can't get it to work, so clearly I'm missing something.

The three fields in the query are Bureau, Section and Unit.

I create an unbound text box on my form (called txtOrg) and in the control source of the text box I type:

=[Bureau] & " " & [Section] & " " & [Unit]

I know there is valid data, as when I run the query upon which the form is based, the fields are populated.

When I view the form, I have a "#Error" displaying in the text box.

Although I have the form is set to view as a form only, if I change it to view as a datasheet, I receive the same error in the field "txtOrg".

Any suggestions? I thought this was straightforward -- I'm sure I've done this before....

 

[Rich]

Are they the same data type?

 

[databasedonr]

Yes -- all three fields that I am trying to concatenate are text fields.

???

 

[IgorB]

You forgot to type in your query
Select [Bureau] & " " & [Section] & " " & [Unit] As MyMerge
and only then there is a way to bound a new virtual filed "MyMerge" to a textbox which must be bound to "MyMerge".

 

[databasedonr]

This is not in the query -- the three fields exist as fields in the query -- I want them to concatenate on the form.

But thanks anyway

 

[IgorB]

Select [Bureau],[Section],[Unit], [Bureau] & " " & [Section] & " " & [Unit] As MyMerge
from yourtable
then follow what I proposed.

 

[databasedonr]

You're absolutely right, IgorB.

I modified the query to produce the contatentation there, and dropped the resultant bound field on the form, and it worked at treat.

Thank you for your suggestion and persistence, although that leaves me wondering why I can't concat the fields on the form....

But thank you, that worked very nicely! I appreciate it.

 

[Rich]

You can concatenate fields directly on your form without having to do it in the query, providing the fields are in the underlying recordset, I suspect you also have the fields as single fields on the form

 

[databasedonr]

Well, Rich, I beat myself up for several hours trying to concatenate them on the form.

As I indicated, the three fields I wanted to contatenate on the form -- Bureau, Section and Unit -- were all included in the query. If I put the three fields on the form individually, they worked, but if I tried to concatenate them to save space, it failed.

I can't explain it!

 

[Banana]

I had the same problem-

I had a table that stores a FirstName and LastName, and they are optional attributes.

Whenever I try to concentate the names together with combobox bound to its ID, the query is nonupdatable. Try the same thing with individual fields and query works.

I'd really like to know exactly why can't this particular table be concentated? I've looked at the relationship as it is the master table to all other tables in the database. AFAIK, all attributes related to lookup tables in this master table are optional, though RI is enforced for all other tables.
RI is enforced for all other tables

 

[ansentry]

Having read the posts in this thread I tried to do what databasedonr wanted in his/her original post.

I could not make it work without changing the name of the controls. Have a look at frmData.

Now the funny part is I imported a table called tblCusGeneric (It is data I use to create samples etc) made a form (frmCusGeneric) based on a query using the table and that works both with referencing both control and ControlName. You will note that the fields that are being referenced in the controls are both numeric and text.

Maybe I am missing something.

 

[B4Geyer]

Concatenating same field from multiple records

ANSentry:

Your sample db showing the different methods is very helpful.
(1) When navigating through each records in your frmCusGeneric the 1st 2 concatenated textboxes appear quite a bit later than the bottom couple done with code. Would this always be the case, that setting this type of "calculation", sort of, would perform better in Vba than in a query? If it's this noticeable on a single machine it seems it would make a huge difference over a network.

(2) Would it be possible to do the same sort of thing when concatenating the same field from multiple records? (i.e., if your customer table had a field for "Salesperson" and you wanted to be able to send specific letters to individual customers, but also wanted to be able to send a salesperson a form letter listing all his/her customers in one sentence.)

Can you give an example of how it would be coded? Thanks, I'm just squeaking into Vba here.

 

[ansentry]

Thank you for your reply, the purpose of my posting and sample was to find out why the 2 forms behave differently.

Now for your question;

If I had a Customer Table I would link it to Salesperson table (1 salesperson Many Customers). If you wanted to select all the customers for salesperson Bill Smith it would be done using a query.

If you wanted to produce a report with all the Customers for Salesperson Bill Smith you could base the report on the same query and user the SalespersonsID to group the records.

Have a look at the attached, is this what you mean.

 

[B4Geyer]

Thanks for the sample, John. I'm actually trying to figure out how to do what you referenced above in order to make a concatenated field that combines the same field from different records, but I think i gave a rather clumsy example of how it would be used.

I have several form letters (currently Word mail merge, but want to change to Access rpts). Some sections in the letter refer to several Consultants involved on a particular Project. (tblProject - tblConsultant, 1-many). I can stop the flow of the letter each time & use a subreport to list all the Consultants vertically, but would prefer to use a text box concatenating their names: James Lau, Sheila Kovacs and Tim Bhati. The only thing connecting the Consultants is the fk ProjectID. Hope that makes more sense.
Thanks very much.

 

[57otto]

dear Databasednr,

the reason why the concatenation is not working, is because "Section" is a reserved word in Access.
I had the same problem when I used "Name".

Replace Section by Section1, and you will see that it is working.
That's also the reason, why the example of ANsentry is working.

greetings,

otto