Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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
0
SteveL13
Asked:
SteveL13
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
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
 
SteveL13Author Commented:
And tblProducts.ProdRef is the field I'm trying to do this with.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now