?
Solved

How display part of a field in a combobox

Posted on 2014-03-14
11
Medium Priority
?
333 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

800 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