RTRIM-SUBSTRING syntax in control Row Source

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
LVL 8
Galisteo8Asked:
Who is Participating?
 
dqmqCommented:
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
 
dqmqCommented:
Try:

Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1
0
 
dqmqCommented:
I mean:
Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dqmqCommented:
Or you can save your working query and just name that in the rowsource
0
 
Galisteo8Author Commented:
I'll try your suggestions this evening.  Thanks.
0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.