Concatenate multiple answers into one field (1 Viewer)

lmcc007 · Dec 5, 2010

First, let me say I may not be asking the correct question or the correct way. But, I have an unbound field that I want to populate with multiple answers into one. I want all the subsidiaries, aka, and fka to become one in to an unbound field separate by a semicolon. That is:

ABC
ABC123
ABCxyz

should be look like this in the unbound field:

ABC; ABC123; ABCxyz

In the query I put:

Code:

Cross Reference: [CompanyNameTitle] & IIf(IsNull([Type]),"",", " & [Type])

How do I get it to loop or continue until the last record?

vbaInet · Dec 5, 2010

Have a look at this generic function from Allen Browne:

http://allenbrowne.com/func-concat.html

Just a side note, it's much better for you to ask the question first and if the helper feels it's necessary to see your db in order to solve the problem, then please do so.

lmcc007 · Dec 5, 2010

vbaInet said:
Have a look at this generic function from Allen Browne:

http://allenbrowne.com/func-concat.html

Just a side note, it's much better for you to ask the question first and if the helper feels it's necessary to see your db in order to solve the problem, then please do so.

Thanks vbaInet,

That's what I am looking for! But, I have a problem. I posted to ControlSource:

Code:

=ConcatRelated("CompanyNameTitle","tblCompanyName","CompanyID = " & [CompanyID])

then I get:

Error 3061: Too few parameters. Expected 1.

Any ideas what I'm doing wrong?

vbaInet · Dec 5, 2010

Is CompanyID Text or Number?

lmcc007 · Dec 5, 2010

vbaInet said:
Is CompanyID Text or Number?

It is Number.

This is what I did--my first junction table--been reading following examples given:

tblCompany

CompanyID dbLong PrimaryKey Indexed
CompanyDateEntered dbDate
CompanyWebsite dbText
CompanySize dbText
CompanyProduct dbText
CompanyYearFounded dbLong

tblCompanyCompanyNameJunction

CompanyID dbLong PrimaryKey ForiegnKey Indexed
CompanyNameID dbLong PrimaryKey ForiegnKey Indexed
Type dbText
DefaultForCompany dbBoolean

tblCompanyName

CompanyNameID dbLong PrimaryKey Indexed
CompanyNameTitle dbText Indexed

Relationships are:

Name: tblCompanyNametblCompanyCompanyNameJunction

Table: tblCompanyName
Foreign Table: tblCompanyCompanyNameJunction
PK: CompanyNameID FK:CompanyNameID

Name: tblCompanytblCompanyCompanyNameJunction

Table: tblCompany
Foreign Table: tblCompanyCompanyNameJunction
PK: CompanyID FK:CompanyID

vbaInet · Dec 5, 2010

Upload the updated db so I can have a quick look.

lmcc007 · Dec 5, 2010

vbaInet said:
Upload the updated db so I can have a quick look.

here it is

vbaInet · Dec 5, 2010

Ok, the table you're using has nothing for the function to work on. That is, there are no records with similar ID. You need to build a query that will pull from the tables in concern for you to have related records.

lmcc007 · Dec 5, 2010

vbaInet said:
Ok, the table you're using has nothing for the function to work on. That is, there are no records with similar ID. You need to build a query that will pull from the tables in concern for you to have related records.

Okay, I did:

Code:

=ConcatRelated("CompanyNameTitle","qryCrossRef","CompanyID = " & [CompanyID])

Is there a way to put 1, 2, 3... in front--meaning: 1) ABC123; 2) ABCxyz; and so on?

vbaInet · Dec 5, 2010

See attached and notice the query and the control source used.

vbaInet · Dec 5, 2010

lmcc007 said:
Okay, I did:

Code:

=ConcatRelated("CompanyNameTitle","qryCrossRef","CompanyID = " & [CompanyID])

Is there a way to put 1, 2, 3... in front--meaning: 1) ABC123; 2) ABCxyz; and so on?

You would have to extract the number part from the string for you to be able to have a custom sort.

lmcc007 · Dec 5, 2010

vbaInet said:
See attached and notice the query and the control source used.

Okay,

I built my query with:

tblCompanyName

CompanyNameTitle

tblCompanyCompanyNameJunction

CompanyNameID
Type
DefaultForCompany

Is that okay? Because I noticed you used all three tables.

lmcc007 · Dec 5, 2010

vbaInet said:
You would have to extract the number part from the string for you to be able to have a custom sort.

No, that's okay--seems like a lot of work. But, thanks for replying!

vbaInet · Dec 5, 2010

I suppose it's whatever exists in the junction table that is needed. So yes you can just use the two but give that some more thought because you understand your setup more than myself.

lmcc007 · Dec 5, 2010

vbaInet said:
I suppose it's whatever exists in the junction table that is needed. So yes you can just use the two but give that some more thought because you understand your setup more than myself.

No, not really. I am just testing--not sure it is correct.

lmcc007 · Dec 5, 2010

vbaInet said:
See attached and notice the query and the control source used.

When I navigate to a record without any cross-references I get the following error:

Code:

Error 3075:  Syntax error (mission operator) in query expression 'CompanyID ='.

lmcc007 · Dec 5, 2010

lmcc007 said:
When I navigate to a record without any cross-references I get the following error:

Code:

Error 3075: Syntax error (mission operator) in query expression 'CompanyID ='.

I think I fix the problem; I went to CompanyID field and enter this as the default:

Code:

=[Forms]![frmCompany]![CompanyID]

vbaInet · Dec 5, 2010

It's your junction table that's the key to the concatenation so those two tables are fine.

lmcc007 · Dec 6, 2010

vbaInet said:
It's your junction table that's the key to the concatenation so those two tables are fine.

Thanks vbaInet for all you help! You have given me some good answers and links; it is very appreciative to get accurate information.

vbaInet · Dec 6, 2010

Happy to assist!

Concatenate multiple answers into one field (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Attachments

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread