Trailing spaces appear in recordset. 1

[qwert231]

Below is a row from a recordset I pull from a SQL2K database. The date is between the quotes (NOT the quotes or parenthesis.)
('A ') ('Z-11-C-F-80 ') ('\\Dell2400\KPDP2\Layouts\FORMAT SENIORS\Z-11-C-F-80.txt ')

I enclosed the data in quotes to better see the trailing spaces that appear. I am trying to do a find on a recordset with as many as 20 records, but the trailing space is throwing it off. For instance, if I search for A itself in the first column, I get 0 results. If I go with a wildcard, I get the wrong record. What can I do?

 

[ebarratt]

Use the Trim function.

if recordset field has trailing or leading spaces that you want to be excluded, say on field ID. then do,

Trim(recordset.fields("ID&quot) and the value ' 9876 ' will become '9876'. Errol Barratt
856-697-1593
New Jersey

 

[qwert231]

Great, if I'm using the value, but what if I wanna do a find on the value, as mentioned in my previous post?

 

[JuanitaC]

Try trimming in your SQL statement:

SELECT RTrim(LTrim(col_name) as [Col_Name]
FROM tbl_Table

 

[qwert231]

K, I see where you are going, tho, not sure about your syntax. Here is what I got now:
"SELECT RTrim(CustomerProducts.ID AS [PackID]), CustomerProducts.ProductID, Products.ProductLayoutPath FROM CustomerProducts INNER JOIN Products ON CustomerProducts.ProductID = Products.LayoutName WHERE CustomerProducts.CustomerID LIKE '" & acct & "' AND CustomerProducts.ProgramID LIKE '" & progType & "'"

(Pretty ain't it.)

 

[JuanitaC]

You need to move the parenthesis to be just around the value you want to trim. I usually use alias names for my tables just to make things a bit shorter and easier to read:

"SELECT RTrim(CP.ID) AS [PackID], CP.ProductID, P.ProductLayoutPath
FROM CustomerProducts CP
INNER JOIN Products P ON CP.ProductID = P.LayoutName
WHERE CP.CustomerID LIKE '" & acct & "'
AND CP.ProgramID LIKE '" & progType & "'"

 

[qwert231]

Yea, I got it. Thanks. Duh for me. I shoulda looked more carefully. Thanks, it works now. Got the code up, the boss happy, and more work >(

Doh, thought I'd get the rest of the day off. That's the way the boss's wind drifts...