Solved

How display part of a field in a combobox

Posted on 2014-03-14
11
326 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Assisted Solution

by:als315
als315 earned 166 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

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.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

776 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