Reorganising data in table field

[hnorgaar]

Need a bit of help to arrange data in a comma based text field, guess by doing a query and create a table from that



Have "100HK, 122HK, 80HK, 90HK"

And would like like that to be sorted by number and end up with

"80HK, 90HK, 100HK, 122HK"

Help appreciated. Thx

[Welshgasman]

You can use the Split() function to get the individual parts.
The sort is up to you, Google for an array sort.
Use & to concatenate back together.

Not the best way to store data TBH?

Or are you talking about putting each of those into their respective field per record, which is how I would have done it in the first place?

[hnorgaar]

hi there, thx for the response, but it actually comes from multi value selection in a form and ends up in a unassigned multi value field on my form, and then copied to a field assigned to a table. However the whole sorting aspect get messed up by being a text field when data need the HK in the end, where the data 100HK is smaller than 80HK. It is for a car database and the reason for doing this is there are many engines on the ame type of cars. BMW 320I with 90HP and 120HP and so on

[Welshgasman]

Most experienced Access developers avoid multi value fields like the plague.
On 2007, they are not available.

Sounds like you would need to write a function to do this any time that field is modified?, if you have to have it sorted like you are asking now?

Really they should be in a separate table, so you would hold 120BHP and other relevant info for that engine, whether it is Diesel or Petrol etc, normally there would be an engine identifier ?

[isladogs]

Most experienced Access developers avoid multi value fields like the plague.
On 2007, they are not available.

Actually they are available. The feature was introduced with A2007.
See Multivalued Fields - Mendip Data Systems and Create or delete a multivalued field - Access (microsoft.com).
Nevertheless, they should definitely be avoided.

[Welshgasman]

Wow, never knew that , but they appear to be tied to lookup values, which after my first DB, I have also not used again.

Thanks anyway Colin.

[isladogs]

Wow, never knew that , but they appear to be tied to lookup values, which after my first DB, I have also not used again.

Thanks anyway Colin.

I certainly don't want to encourage you to use MVFs but, as my article explains, they can be created either from a value list or from a lookup table

[hnorgaar]

I certainly don't want to encourage you to use MVFs but, as my article explains, they can be created either from a value list or from a lookup table

And I dont know any other way to do it than with cascading lists and MVF. Im in the situation where I do have the data where every car have 1 engine and HP, but I need to combine the HP per Engine/Car for export to Excel and therefore have less lines/duplicates and in the end fewer articles for the custemer to choose from. Thx for the assistance

[Gicu]

Please have a look at the attached sample - query 1.

Cheers,
Vlad

[hnorgaar]

Thx Gicu, I learned something new today and solved that issue perfectly. Case closed

[Gicu]

You're welcome!