Good day all,

I am hoping anyone can give me a start as to how to solve my issue below.

I have a field that contains values separated by a comma. Of these choices I only want those that meet my criteria.

Example. My field contains WD,LI,AI,LD (all separated by a comma), Id like the query only show WD and not the other 3.

Any help would be greatly appreciated.

You are going to probably need a UDF to do this, involving the Split function.

Darn, I was affraid of that being the answer.

Thank you for input.

i am confused. if you field has WD,LI,AI,LD what do you mean by only showing the WD? What would happen to the other data in the field?

If you are using Access 2007, it has multi-value fields, which are probably a bad idea, but the Like operator will work on these fields.

frank,

I got that part, but if the field has items besides the UD it would still show those as well when getting all fields that contain the UD. right?

Hello Bob and Thank you for your reply.

What I mean is that I would like to write for example and IIF statement that says if the field contains "W*" then place the entire result in a different column.

I need to write a few different statements so there would be one for IIF column contains "L*" place in an entirely different column.

Hopefully that helps and I did not further confuse anyone.

By the way, unforunately I am using Access 2003 =(.

Do you want something like the following?

Field: WDColumn: IIF(SomeField Like "*WD*","WD",Null)

Field: LiColumn: IIF(SomeField Like "*LI*","LI",Null)

Field: AIColumn: IIF(SomeField Like "*AI*","AI",Null)

Hello John and thank you for your reply.

I am looking for something like that. Enclosed is what I currently have and the results it produces.

Formula

Customer_ID: IIf([Customer ] Like "L*",[Customer],IIf([Customer] Like "A*",[Customer],""))


Results
AI, LI, AS, DL, LS, LV, AV, TI, WD

Unforunately the above function will return the entire row rather than just those that meet the criteria of "L*" or "A*" criteria.

i dont have 2003 and John seems well on the way.

Good luck with the rest of the project

It's going to take some more work to get what you want. Perhaps this:


Customer_ID: IIf([Customer ] Like "L*",Left$([Customer], 2),IIf([Customer] Like "A*",Left$([Customer], 2),""))

My code has assumed that the "interesting" parts of [Customer] are always the leftmost two characters. If other characters may be involved, then the code will definitely be different, involving Mid$, and InStr.

Thank you very much for your reply as it has helped, however I believe I failed to mention that there would be instances that would indicate that there could be a multiple of instances within the same cell.

Example, there could possibly be a value of both "L" as well as "A" and I would need to account for these within the code.

Any further assistance is greatly appreciated.

I would like to help, but at this point I am confused on what you want.

Given that a field contains WD,LI,AI,LD
What results exactly do you want?
Do you want a field (column) that contains WD and another that contains LI and another that contains AI and another that contains LD?
Do you want a field that contains All four values if (for instance) LI is contained in the string?

Or do you want one field that contains WD and LI if you are looking for those two values?

Thank you John as I greatly appreciate your willingness to assist.

The field entitled "Customer" for all purposes contains various results separated by a comma. There are over 50 different entry variations that may be made so the best way I figured to do this was to use a wild card search as each character in the series "WD", "LI", "AI" etc etc is actually a separate category. So Like "W*" would also be WD,WV,WI, WX etc etc.

Now to the tricky part, so all of these answers are within the category "Customer"

Now I need a new Field so that I may separate them into their appropriate categories based upon the first letter in the series. The tricky part here is that there are like categories that will need to be grouped. So LIKE "W*" and LIKE "A*" will be grouped in one Field we'll call this field "CUSTOMER_2".

Example here is using the previous example of "WD,LI,AI,LD "

Under the new Category..."Customer 2" we will have WD,WV,WI,AI

I hope this did not confuse anyone but in fact give more detail into my dilemma.

Perhaps a custom VBA function will work for you.

You would call the following from your query.

Field: Customer2: fGroups([Customer],"W","A")

If you need more values to match just add them on to the end as an added string. You could even match by two letters if needed. Something like

Field: Customer2: fGroups([Customer],"W","A")

Public Function fGroups(strIn, ParamArray strValues())
Dim sItems As Variant
Dim i As Integer, i2 As Integer
Dim strReturn As String

sItems = Split(strIn, ",")

For i = LBound(sItems) To UBound(sItems)
For i2 = LBound(strValues) To UBound(strValues)
If Trim(sItems(i)) Like strValues(i2) & "*" Then
strReturn = strReturn & ", " & Trim(sItems(i))
End If
Next i2
Next i

fGroups = Mid(strReturn, 3)

End Function

I don't understand how you get WD, WV, WI, AI from WD, LI, AI, LD. Are you saying that any W match should return WD, WV, WI?

Perhaps then you need a table with two fields - MatchValue and ReturnValue.
With records like
W : WD, WV, WI
A : AI
L : LD, LL
...

Then you could either build a custom function to lookup and concatenate the values or use one of the concatenate VBA functions that are already written.