MS Access VBA Split function to DLOOKUP each value?

[SIMMS7400]

Hi Folks -

I have a SharePoint table that is populated based on user submission. We give the end users the ability to multi-select for this one field, which concatenates all values with a ";". From an end user perspective, we make visible to them the 'Description' field for this particular data element, and then hide the unique ID field.

The fields look like this on the "PPSTable":

IndicationAlias	IndicationID
Chicken;Steak;Veal	IND-00056;IND-00095;IND-00126

We have another table called "IndicationMaster" that contains all IndicationID and IndicationAlias values and is considered the "Master" and the version of the truth. The IndicationAlias values do change sometimes.

IndicationID	IndicationAlias
IND-00056	Chicken Cutlet
IND-00095	Steak
IND-00126	Veal

Therefore I need to be able to split apart the IndicationID values from the "PPSTable" and then lookup the "IndicationMaster" table to grab the applicable IndicationAlias and then re-concatenate. Or is there a better way?

[Welshgasman]

If you had not stored the alias in the first place and just the IDs, you would not have this problem??

You could use the Splt() function to get the individual values and then process as you need, but you will need to do this all the time.

Unless you need to know at a particular time it was just Chicken, and now it is Chicken Cutlet, then there should be no need to store it?

[SIMMS7400]

Thank you! Unfortunately, I do need to store those values as we are pushing those values to another system.

[Welshgasman]

Thank you! Unfortunately, I do need to store those values as we are pushing those values to another system.

Only you know your system, but part of that process could be concatenating those values as you enquired in another thread. Up to you?

The fact that you need to change them now, leads me to think that would be a better method? Every time a master changes, you need to run this process.? Wha if someone forgets?, easily done?

I once worked at a bank where they should have run a process to add bonus interest on maturity, but they missed it for some reason, did not realise for a few days, and then muggins here had to fix the problem

You could just create a dedicated function to concatenate them and pass the PK ID that would get all the IndicationIDs lnked to that PK?

Most people would create individual records for each.?

Think of an Order and the Order Items for that order?

Good luck anyway.

[Micron]

why does this thread seem so familiar to this one?
https://www.accessforums.net/showthr...183#post473183

[SIMMS7400]

If by familiar you mean the OP, then yes. Otherwise, completely different. Thanks though!