How can I check if the columns in my table have trailing spaces in MS ACCESS 2003?
How can I check if any of my column values in TABLEA consist of trailing spaces? I only know how to check if there are spaces from a single column, but not all.
SELECT *
FROM TABLEA
WHERE Col1 LIKE ('* ');
Since I have 45 columns in my table, it will be very tedious to specify everything in the where clause. I am currently doing this manually using columns and rows, any better suggestion?
Thank you in advance
source to share
RTrim 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 original values. If not, your best bet is to insert these values into the temp table.
INSERT INTO TempTable (Col1, Col2,....)
Select RTrim(Col1) As TrimmedCol1, Rtrim(Col2) As TrimmedCol2,. . . .
From TABLEA
If you just want to get lines with trailing whitespace, you can do:
SELECT * FROM TableA
WHERE
Col1 <> RTrim(Col1) OR
Col2 <> RTrim(Col2) OR
...
Col44 <> RTrim(Col44)
source to share
I don't know exactly what you want. If you only want the names of text columns whose values include trailing spaces, you can use a custom VBA function.
I have added trailing spaces to values in 2 columns in my table tblFoo
. Using the function below, I get the names of these 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 want to strip out trailing spaces, you can do so by modifying the function to create and execute a statement UPDATE
(instead of 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 columns of text contain one or more trailing spaces, you can build a query whose clause WHERE
refers to the field names returned by the function ColumnsWithTrailingSpaces()
.
SELECT * FROM TABLEA WHERE Right(Col1, 1) = ' ' OR Right(Col9, 1) = ' ' OR Right(Col42, 1) = ' '
If none of these suggestions give you what you want, please clarify what you want .; -)
source to share
AFAIK the only way to do this is to specify all columns:
SELECT *
FROM TABLEA
WHERE Col1 LIKE ('* ')
OR Col2 LIKE ('* ')
OR ...
Suggestion: Copy the table definition (expression CREATE TABLE
) into a text editor and do some magic search and replace operation to create the statement.
source to share