Separate Contents of a Field in a Query

[Holly]

I have a field called sku. In a query I want to break this field (sku) into two separate pieces.


A sku contains contents which describe both the color and size of a T-shirt. For example:

See image below.

I want to be able to sort everything that exists BEFORE the 4^th hyphen and then also sort by everything AFTER the 4^th hyphen. I only know how to do a Left(), Right(), or Mid() but since the number of characters vary in both the color and the size, I don’t think these will work.

Any help will be greatly appreciated.

[jwhite]

I'm not getting it. Seems to me if you just do an Ascending sort on SKU it would be in the order you want. If not, arrange the records below in the order you want and re-post it.

SKU
Desc

HI-PKT-LS-asphalt-2xlt
= an asphalt colored shirt that is size 2XLT

HI-PKT-LS-asphalt-lt
= an asphalt colored shirt that is size

HI-PKT-LS-asphalt-mt
= an asphalt colored shirt that is size

HI-PKT-LS-asphalt-st
= an asphalt colored shirt that is size

HI-PKT-LS-asphalt-xlt
= an asphalt colored shirt that is size

HI-PKT-LS-azalea-2xlt
= an azalea colored shirt that is size

HI-PKT-LS-azalea-lt
= an azalea colored shirt that is size

HI-PKT-LS-azalea-mt
= an azalea colored shirt that is size

HI-PKT-LS-azalea-st
= an azalea colored shirt that is size

HI-PKT-LS-azalea-xlt
= an azalea colored shirt that is size

[jwhite]

Looking at it again... The SKU shouldn't include the description of the item. You should have a Products table that has (amongst other fields) Style and Size. Style and Size would be a Foreign Key to tblStyles and tblSizes respectively. If you want to keep using "lt", "xlt" and "2xlt", then you will need to add a SortOrder field to tblSizes to get the order you want. With that, sorting would be very easy.

[John_G]

Check out the instrrev function - does just what you want to do, i.e. looks for the first occurance of one string in another, but starting from the right to find it.

for example: instrrev("aa-b-1234-fffff-34","-") yields 16, the position of the first hyphen from the right.

[andy49]

The split command


Function splitskucokour (skutext as string) as text

Dim skuarray(6)
Skuarray =Split("skutext", "-")

splitskucokour =Skuarray(4)

End function

The function can be called within a query

A similar function splits a text field into smaller values in an array based on a separator (in this case a "-")

Function splitskusize(skutext as string) as text

Dim skuarray(6)
Skuarray =Split("skutext", "-")

splitskusize =Skuarray(5)

End function

Would get the size too all from same query


Sent from my iPhone using Tapatalk