[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

RTRIM-SUBSTRING syntax in control Row Source

Posted on 2006-04-26
8
Medium Priority
?
421 Views
Last Modified: 2011-08-18
In my Access 2000 ADP (it's got a SQL Server 2000 back-end), I've got table values (LotNum) that include a dash. On one of my forms, there's a combo box that is supposed to display the first part of the LotNum values; that is, the part before the dash.  Here's what I've got as the combo box Row Source:
----------------------------------
SELECT DISTINCT
    RTRIM(SUBSTRING(dbo.LOT.LotNum, 1,
    ISNULL(NULLIF (CHARINDEX('-', dbo.LOT.LotNum) - 1, - 1),
    len(dbo.LOT.LotNum)))) as Shipment
FROM dbo.LOT
WHERE [Lot].[Client] = 'Bob'
----------------------------------

This query works just fine in Design View; when I test it, I get a list of values without the dash. But when I use it in the Row Source of the combo box, the results still include the dash.  Is there some weird SQL-to-Access syntax thing I'm not catching?

--Galisteo8
0
Comment
Question by:Galisteo8
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 16550385
Try:

Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1
0
 
LVL 42

Expert Comment

by:dqmq
ID: 16550389
I mean:
Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 16550397
Or you can save your working query and just name that in the rowsource
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Author Comment

by:Galisteo8
ID: 16553969
I'll try your suggestions this evening.  Thanks.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 16557190
One more thing related to this rtrim command...
I am trying to select *distinct* values.  Some values are similar but different (due to user typos, I imagine), for example:
"XYZ-123" and "XYZ -456" should produce the following results from the query -- "XYZ" and "XYZ ".  (Note that the second value has a trailing space on it.)  When I got my query above to work in Design View, only "XYZ" was in the results.  As I prepare to test your suggestions for getting this query to work in my combo box row source, I am also wondering... can I "force" the trailing space to be recognized so that I get BOTH "XYZ" and "XYZ " in my pulldown?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 400 total points
ID: 16558358
If the access syntax works, it will preserve the trailing space.

In SQL server, the equivalent is:


Select distinct left(dbo.lot.lotnum,charindex('-',dbo.lot.lotnum + '-')-1)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 16593156
dqmq,

I tried using...
Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1)
...in my combo box RowSource, but got an error that 'instr' is not a recognized function name.

I would just set up the query (view) in SQL and have the combo box point to it for its RowSource, but the WHERE phrase needs to derive a value from a text control on the form -- it doesn't really say WHERE [Lot].[Client] = 'Bob', but rather it says WHERE [Lot].[Client] = '" & cmbBrandLot & "'".

Can I set up the query/view in SQL if part of the query/view needs to get a value from a form control?

--Galisteo8


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 16602647
Wait a second...  Upon further testing, I see that the query is already doing what I need it to do.  I used your SQL syntax since ‘instr’ wasn’t working for me – plus, the query is actually assigned to the combo box based on the user’s selection in *another* combo box (cmbBrandLot) – it gets assigned via the OnClick event of cmbBrandLot.  Here's the query now being assigned for the Row Source:

SELECT DISTINCT LEFT(dbo.lot.lotnum, charindex('-',dbo.lot.lotnum + '-')-1) FROM dbo.LOT WHERE [Lot].[Client] = '" & cmbBrandLot & "'"

So, the combo box pulldown now properly shows distinct values without the dashes... and for purposes of "distinct values" it appears to ignore any trailing spaces (so, for example, "XYZ" would be displayed, but not "XYZ ").  However, when the user is finished with the combo boxes they click a command button to open another form that is filtered by the values that they selected... and on this form they still see records related to BOTH "XYZ" and "XYZ " values.  So this seems to be working as intended.

Thanks for your help
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

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.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

865 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