Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How display part of a field in a combobox

Posted on 2014-03-14
11
Medium Priority
?
335 Views
Last Modified: 2014-03-15
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
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39930435
what is the rowSource of the combo box?
0
 

Author Comment

by:SteveL13
ID: 39930442
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
 

Author Comment

by:SteveL13
ID: 39930444
And tblProducts.ProdRef is the field I'm trying to do this with.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39930459
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
 

Author Comment

by:SteveL13
ID: 39930472
Hmm that didn't work.  Now the combo box retuned nothing.
0
 

Author Comment

by:SteveL13
ID: 39930479
And if I just try to run the query I get the attached error.
error.jpg
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39930692
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 668 total points
ID: 39930704
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
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 668 total points
ID: 39930707
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
 
LVL 40

Assisted Solution

by:als315
als315 earned 664 total points
ID: 39931139
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
 

Author Closing Comment

by:SteveL13
ID: 39931385
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question