Looking for a ‘-9’ in a string (A2k2)

Topic

New Reply

Hello All,
I am using a form control to look up a part number in a ODBC connected table. The right hand part of the string is “-9” for the part numbers I want returned. The hard part is that there is some spaces at the end of the number. I would like to say that there will always be 4 spaces but I cannot say for sure. So…… Is there a way to strip off the spaces at the end of the string and then look for anything that has the “-9”??? Here is an example of one of the part numbers (four end spaces included):
050-10029-9

Thanks,
Mark

Reply | Quote

Replies

Try:

SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right([MyFieldName],2)=”-9″))

Reply | Quote

Shouldn’t that be WHERE Right(Trim([MyFieldName]),2) = “-9” or, alternatively, WHERE Trim([MyFieldName]) Like “*-9”

Reply | Quote

Hans,

The SQL I posted seemed to work but now I am not so sure. In a Select statement with a Where, in which order is the SQL processed?

James

Reply | Quote

Whatever the order of execution, in

SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right([MyFieldName],2)=”-9″))

the WHERE condition is on MyFieldName, not on the trimmed field XX. You won’t notice the difference in a test in Access, since Access automatically trims trailing spaces when you enter data in a text field. But Mark Santos is working with a table in another format, linked via ODBC. Text fields in such a table may well contain trailing spaces.

Reply | Quote

Thanks. Learned something new today.

Reply | Quote

Thanks. Learned something new today.

Reply | Quote

Whatever the order of execution, in

SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right([MyFieldName],2)=”-9″))

the WHERE condition is on MyFieldName, not on the trimmed field XX. You won’t notice the difference in a test in Access, since Access automatically trims trailing spaces when you enter data in a text field. But Mark Santos is working with a table in another format, linked via ODBC. Text fields in such a table may well contain trailing spaces.

Reply | Quote

Hans,

The SQL I posted seemed to work but now I am not so sure. In a Select statement with a Where, in which order is the SQL processed?

James

Reply | Quote

Shouldn’t that be WHERE Right(Trim([MyFieldName]),2) = “-9” or, alternatively, WHERE Trim([MyFieldName]) Like “*-9”

Reply | Quote

I think you will have to change it to:
SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right(Trim([MyFieldName]),2)=”-9″))
or
SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right(XXX,2)=”-9″))

Reply | Quote

I think you will have to change it to:
SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right(Trim([MyFieldName]),2)=”-9″))
or
SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right(XXX,2)=”-9″))

Reply | Quote

Try:

SELECT Trim([MyFieldName]) AS XX
FROM MyTableName
WHERE ((Right([MyFieldName],2)=”-9″))

Reply | Quote