Using a String as Columns-CodePudding

my first post and my first project with MS Access and VBA, and really coding in general...!

I have an an Excel datatable txlsRegistration with multiple similar columns

[LON Option 1 (London Borough)]
[LON Option 1 (Railway Station)]
[BRS Option 1 (Postcode District)]
[BRS Option 1 (Public Transport Hub)]

etc

All these fields need to concatenated on the Option number in order to effectively query them later.

So I have a Select Query in Access which concatenates all the same Option number fields together and works fine.

SELECT txlsRegistration.ID, [txlsRegistration]![LON Option 1 (London Borough)] & [txlsRegistration]![LON Option 1 (Railway Station)] & [txlsRegistration]![BRS Option 1 (Postcode District)] & [txlsRegistration]![BRS Option 1 (Public Transport Hub)] AS ropOption1
FROM txlsRegistration

However, the list of fields is growing, and I want a way to automate the creation of this concatenation in the select query.

I have written a For...Each procedure in VBA called GetFields which goes through the table's fields and finds all the Option 1 fields and returns them as a String i.e. [txlsRegistration]![LON Option 1 (London Borough)] & [txlsRegistration]![LON Option 1 (Railway Station)] & [txlsRegistration]![BRS Option 1 (Postcode District)] & [txlsRegistration]![BRS Option 1 (Public Transport Hub)]

When I create a new select query using my Function as follows

SELECT txlsRegistration.ID, GetFields() AS ropOption1
FROM txlsRegistration

it simply returns the actual string against each record. What I want is for the select query to recognise the string as the column names, not a literal string. Is there a way I can do that?

Many thanks on my first venture...!

CodePudding user response：

I have created the query in VBA using QueryDefs of which I wasn't previously aware!