How display part of a field in a combobox

I have a field in a combobox that looks like:

Applesauce, 19x25, Green, Vertical, Chicago

But I want it to display everything up to the last comma like:

Applesauce, 19x25, Green, Vertical

In other words don't display the ", Chicago" part of it.

How can I do this?

--Steve
SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try running this query


SELECT tblProducts.ProductId, Left([tblProducts].[ProdRef],InStrRev([tblProducts].[ProdRef],",")-1) FROM tblProducts
GROUP BY tblProducts.ProductId, Left([tblProducts].[ProdRef],InStrRev([tblProducts].[ProdRef],",")-1)
ORDER BY Left([tblProducts].[ProdRef],InStrRev([tblProducts].[ProdRef],",")-1)


post the result
0
 
Rey Obrero (Capricorn1)Commented:
what is the rowSource of the combo box?
0
 
SteveL13Author Commented:
Here is the SQL of the query:

SELECT tblProducts.ProductID, tblProducts.ProdRef, tblLocations.LocationDesc, Sum([IncomingQty]-[SoldQty]) AS [Inv Qty]
FROM (tblProducts LEFT JOIN tblLocations ON tblProducts.LocationID = tblLocations.LocationID) RIGHT JOIN tblInventoryDetails ON tblProducts.ProductID = tblInventoryDetails.ProductID
GROUP BY tblProducts.ProductID, tblProducts.ProdRef, tblLocations.LocationDesc
HAVING (((tblProducts.ProdRef) Like "*" & [Forms]![frmSearchForProductsOrImages]![cboProduct] & "*"))
ORDER BY tblProducts.ProdRef, tblLocations.LocationDesc;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SteveL13Author Commented:
And tblProducts.ProdRef is the field I'm trying to do this with.
0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT tblProducts.ProductID, Left([tblProducts].[ProdRef], InstrRev([tblProducts].[ProdRef],",")-1), tblLocations.LocationDesc, Sum([IncomingQty]-[SoldQty]) AS [Inv Qty]
FROM (tblProducts LEFT JOIN tblLocations ON tblProducts.LocationID = tblLocations.LocationID) RIGHT JOIN tblInventoryDetails ON tblProducts.ProductID = tblInventoryDetails.ProductID
GROUP BY tblProducts.ProductID, Left([tblProducts].[ProdRef], InstrRev([tblProducts].[ProdRef],",")-1), tblLocations.LocationDesc
HAVING (((Left([tblProducts].[ProdRef], InstrRev([tblProducts].[ProdRef],",")-1)) Like "*" & [Forms]![frmSearchForProductsOrImages]![cboProduct] & "*"))
ORDER BY Left([tblProducts].[ProdRef], InstrRev([tblProducts].[ProdRef],",")-1), tblLocations.LocationDesc;
0
 
SteveL13Author Commented:
Hmm that didn't work.  Now the combo box retuned nothing.
0
 
SteveL13Author Commented:
And if I just try to run the query I get the attached error.
error.jpg
0
 
COACHMAN99Commented:
create a function with a while loop incrementing a counter, character by character until there are no more commas, then return the counter and use it to derive right(string, len(str)-counter)
0
 
COACHMAN99Commented:
select Right(tststr, Len(tststr) - GetRightPart(tststr))

Public Function GetRightPart(str As String) As Long
  On Error GoTo err_GetRightPart
  Dim countt As Long
  countt = 1
  While InStr(countt, str, ",", 1) > 0
    countt = countt + 1
  Wend
  GetRightPart = countt - 1
exit_GetRightPart:
  Screen.MousePointer = 0
  Exit Function
err_GetRightPart:
  MsgBox "Error " & Err.Number & ", " & Err.Description & " in Sub GetRightPart"
  Resume exit_GetRightPart
End Function
0
 
als315Commented:
I'm with COACHMAN99. For such cases I prefer to use functions. You can use this:
Function lpart(Str As String) As String
Dim l As Long
For l = Len(Str) To 1 Step -1
    If Mid(Str, l, 1) = "," Then Exit For
Next l
lpart = Left(Str, l - 1)
End Function

Open in new window

Place function to Module and you will be able to use it in your query:
SELECT tblProducts.ProductID, lpart(tblProducts.ProdRef), tblLocations.LocationDesc, Sum([IncomingQty]-[SoldQty]) AS [Inv Qty]
FROM (tblProducts LEFT JOIN tblLocations ON tblProducts.LocationID = tblLocations.LocationID) RIGHT JOIN tblInventoryDetails ON tblProducts.ProductID = tblInventoryDetails.ProductID
GROUP BY tblProducts.ProductID, lpart(tblProducts.ProdRef), tblLocations.LocationDesc
HAVING (((tblProducts.ProdRef) Like "*" & [Forms]![frmSearchForProductsOrImages]![cboProduct] & "*"))
ORDER BY tblProducts.ProdRef, tblLocations.LocationDesc;

Open in new window

0
 
SteveL13Author Commented:
Thanks to all three of you.  I ended up using Rey's suggestion for timings sake but will keep record of the other two also.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.