CODEWITHSUNDEEP
sqlms-accessms-access-2003
10e5x
10e5x

Reputation: 909

How to check if any value of the columns in my table consist trailing spaces in MS ACCESS 2003?

How can i check if any of the values of my columns in TABLEA consist trailing spaces? I only know how to check if there are trailing spaces from one column but not all. 

       SELECT *  
       FROM TABLEA
       WHERE Col1 LIKE ('* ');

As i have 45 columns in my table, it will be very tiring to specify all in the where clause. Currently i am doing it manually by tab - ing through the columns and rows, any better suggestion?

Thanks in advance

Upvotes: 3

Views: 3438

Answers (3)

HansUp
HansUp

Reputation: 97101

I am uncertain about exactly what you want. If you want only the names of the text columns whose values include trailing spaces, you can use a custom VBA function.

I added trailing spaces to values in 2 columns in my tblFoo table. Using the function below, I get the names of those columns in the Immediate window.

? ColumnsWithTrailingSpaces("tblFoo")
some_text, Trans Type

Public Function ColumnsWithTrailingSpaces(ByVal pTable As String) As String
    Dim db As DAO.database
    Dim fld As DAO.Field
    Dim strReturn As String

    Set db = CurrentDb
    For Each fld In db.TableDefs(pTable).Fields
        Select Case fld.Type
        Case dbText, dbMemo
            If DCount("*", pTable, _
                "Right([" & fld.Name & "], 1) = ' '") > 0 Then
                strReturn = strReturn & ", " & fld.Name
            End If
        Case Else
            'pass
        End Select
    Next fld
    Set fld = Nothing
    Set db = Nothing
    If Len(strReturn) > 0 Then
        strReturn = Mid(strReturn, 3)
    End If
    ColumnsWithTrailingSpaces = strReturn
End Function

If you also wanted to discard the trailing spaces, you could do that by modifying the function so that it creates and executes an UPDATE statement (instead of just adding the field name to strReturn).

strUpdate = "UPDATE [" & pTable & "]" & vbCrLf & _
    "SET [" & fld.Name & "] = RTrim([" & fld.Name & "]);"
db.Execute strUpdate, dbFailOnError

If you want to see all those rows where any of the text columns includes one or more trailing space, you can build a query whose WHERE clause references the field names returned by the ColumnsWithTrailingSpaces() function.

SELECT *
FROM TABLEA
WHERE
       Right(Col1,  1) = ' '
    OR Right(Col9,  1) = ' '
    OR Right(Col42, 1) = ' '

If none of those suggestions gives you what you want, please clarify what you want. ;-)

Upvotes: 2

Edwin
Edwin

Reputation: 1468

Do an RTrim on all your columns and insert them back

Select RTrim(Col1) As TrimmedCol1, Rtrim(Col2) As TrimmedCol2,. . . . 
From TABLEA

If you have a Counter for the primary key, you can insert the result into the same table and delete the origional values. If not, the best way is to insert these values into a temp table.

INSERT INTO TempTable (Col1, Col2,....)
 Select RTrim(Col1) As TrimmedCol1, Rtrim(Col2) As TrimmedCol2,. . . . 
    From TABLEA

If you would like to just get the rows with trailing spaces, you can do:

SELECT * FROM TableA
WHERE 
Col1 <> RTrim(Col1) OR
Col2 <> RTrim(Col2) OR
...
Col44 <> RTrim(Col44)

Upvotes: 2

lc.
lc.

Reputation: 116498

AFAIK the only way to do it is to specify all columns:

SELECT *
FROM TABLEA
WHERE Col1 LIKE ('* ')
OR Col2 LIKE ('* ')
OR ...

Suggestion: Copy the table definition (CREATE TABLE statement) out to a text editor and do some magic search-replace to create the statement.

Upvotes: 0

Related Questions