asked on

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

dqmq

Try:

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

dqmq

I mean:
Select distinct left(dbo.lot.lotnum,instr(dbo.lot.lotnum & "-","-")-1)

dqmq

Or you can save your working query and just name that in the rowsource

Galisteo8

ASKER

I'll try your suggestions this evening. Thanks.

Galisteo8

ASKER

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?

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Galisteo8

ASKER

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

Galisteo8

ASKER

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