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:
    RTRIM(SUBSTRING(dbo.LOT.LotNum, 1,
    ISNULL(NULLIF (CHARINDEX('-', dbo.LOT.LotNum) - 1, - 1),
    len(dbo.LOT.LotNum)))) as Shipment
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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1
I mean:
Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1)
Or you can save your working query and just name that in the rowsource
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Galisteo8Author Commented:
I'll try your suggestions this evening.  Thanks.
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?
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Galisteo8Author Commented:

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?


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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.