ConcatRelated

[grundig1987]

Good Afternoon,

I have a group of related records which I would like to concatenate into one string - The sample data is below:

StudentID CourseID
B787878 BSB070
B787878 BSC880
B787878 BSD808

And the desired outcome would be:
B787878 BSB070, BSC880, BSD808

I have used a comma as a delimiter but to be honest anything can be used as I will be storing as an array afterwards so as long as something can be used to do this that will be fine.

This is far as I have got - I have a form with 2 text boxes. One is called Student_ID_2, and the other Text120 - Student_ID_2 takes the current users StudentID (irrelevant code) - Text120 should look up that StudentID in the table named 'Enrolments' and return all of the CourseID's, concatenated.

In text120, I have set the control source as =concatrelated("[CourseID]","[Enrolments]","[StudentID = '" & [Student_ID_2] & "'")

This does not appear to be doing anything when the Student_ID_2 text box is set to B787878.

Any suggestions?

Thanks in advance,

Mark

 

[RuralGuy]

This link should be helpful: http://allenbrowne.com/func-concat.html

 

[grundig1987]

This link should be helpful: http://allenbrowne.com/func-concat.html

RuralGuy - Thanks for the quick reply, I have already visited this site, and the example on the site is the one I modelled my piece of code on, which appears to be incorrect so somewhere I have made a mistake with syntax or something, because nothing is appearing in the text box.

As far as I can see, the example given on the website:

=ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

Should be used in my example as:

=concatrelated("[CourseID]","[Enrolments]","[StudentID = '" & [Student_ID_2] & "'")

But this does not seem to be working!

 

[RuralGuy]

=concatrelated("[CourseID]","[Enrolments]","[StudentID] = '" & [Student_ID_2] & "'")
...and I assume [StudentID] is a String field?

 

[grundig1987]

=concatrelated("[CourseID]","[Enrolments]","[StudentID] = '" & [Student_ID_2] & "'")
...and I assume [StudentID] is a String field?

I added the bracket which I missed out for some reason, and this does not seem to make a difference. The result of the text box is #Name?

StudentID is a text field if that helps.

Thanks,

Mark

 

[RuralGuy]

I added the bracket which I missed out for some reason, and this does not seem to make a difference. The result of the text box is #Name?

StudentID is a text field if that helps.

Thanks,

Mark

That is usually an indication that one of the names is not spelled correctly.

 

[grundig1987]

That is usually an indication that one of the names is not spelled correctly.

Hmmm, I have doubled checked all the spelling and everything is fine. I have also made sure there is an = sign before the expression in the control source property box which can also be a cause of the error, but still getting the same error

 

[RuralGuy]

You did not name your Standard Module the same name as the Function, did you? That's a no...no.

 

[grundig1987]

You did not name your Standard Module the same name as the Function, did you? That's a no...no.

I'll be honest RG - What you have just said has gone right over my head! I haven't set up a function in VBA - All I have done is put that piece of code into the control source text box on a form! I can't be much more technical than that I am afraid!

 

[RuralGuy]

How to use the function
Add the function to your database:

1.In Access, open the code window (e.g. press Ctrl+G.)
2.On the Insert menu, click Module. Access opens a new module window.
3.Paste in the function below.
4.On the Debug menu, click Compile, to ensure Access understands it.

Did you do as the instructions asked?

 

[grundig1987]

Did you do as the instructions asked?

RG - I am officially a moron! Thanks for alerting me to this, and cheers for the help!

 

[RuralGuy]

Everyone has to start somewhere. Is it now working?

 

[grundig1987]

Everyone has to start somewhere. Is it now working?

RG - It is working great now thank you!

 

[RuralGuy]

Ahh...another satisfied customer!

 

[McP1sr]

Hi gents. I just created an account to try and communicate to the author - thank you so much for the code. Saved me tons of time, years after it was written

 

[RuralGuy]

Glad we could help. Welcome to the site!