Want to trim the strings, and nothing else...

[Maury Markowitz]

I'm copying rows from one table to another. The data in the "from" table
needs to be trimmed, as it often has extra spaces and other garbage. However
the same data also includes floats, ints and dates.

Is there some way to say "if this is a string, trim it"? I tried xxx.Type,
but that doesn't work for fields, only dim'ed types.

Maury

 

[Dirk Goldgar]

I'm copying rows from one table to another. The data in the "from"
table needs to be trimmed, as it often has extra spaces and other
garbage. However the same data also includes floats, ints and dates.

Is there some way to say "if this is a string, trim it"? I tried
xxx.Type, but that doesn't work for fields, only dim'ed types.

I take it that you are writing a general-purpose routine to copy the
rows; else you would just hard-code "Trim()" around the specific fields
you know are text. Could you post the code you're trying to make work?

 

[Maury Markowitz]

I take it that you are writing a general-purpose routine to copy the
rows;
Exactly.

else you would just hard-code "Trim()" around the specific fields
you know are text. Could you post the code you're trying to make work?

Sure! (missing outer loop over lines in the file)

fieldcount = 0
While fieldcount < file.Fields.Count
aField = file.Fields(fieldcount)

' If aField.Type = dbChar Then aField = Trim(aField)

activity.Fields(fieldcount) = aField
fieldcount = fieldcount + 1
Wend

 

[Dirk Goldgar]

Sure! (missing outer loop over lines in the file)

fieldcount = 0
While fieldcount < file.Fields.Count
aField = file.Fields(fieldcount)

' If aField.Type = dbChar Then aField = Trim(aField)

activity.Fields(fieldcount) = aField
fieldcount = fieldcount + 1
Wend

Are "file" and "activity" ADODB.Recordset objects? How is "aField"
declared?

 

[Maury Markowitz]

Are "file" and "activity" ADODB.Recordset objects? How is "aField"
declared?

File is currenly a linked table due to the problems I'm having getting the
ODBC Text driver to understand semicolons. I use OpenRecordset to access it.
activity is an ADOBD recordset to a SQL Server table. The records in file are
being copied (with some additional information) over to activity.

aField is not DIMed.

 

[Dirk Goldgar]

File is currenly a linked table due to the problems I'm having
getting the ODBC Text driver to understand semicolons. I use
OpenRecordset to access it. activity is an ADOBD recordset to a SQL
Server table. The records in file are being copied (with some
additional information) over to activity.

aField is not DIMed.

Tsk! Dim *everything* and the compiler will catch a lot of your errors
for you.

I'd be happier if I could see more of the code, but I'm going to proceed
based on some assumptions. I'm not sure whether "file" is an ADODB
recordset or a DAO recordset. If you're working in an MDB file, as
implied by your reference to a file being a linked table, I expect it's
a DAO recordset, but I could be wrong.

Since aField is not declared, it's a variant. Since you use the syntax
....

aField = file.Fields(fieldcount)

.... (not a Set statement) aField will be holding only the value of
file.Fields(fieldcount), not a reference to the Field object itself.
Therefore aField won't have a Type property. You could use the VarType
function to check the type of aField, or you could check the Type
property of file.Fields(fieldcount).

I would probably rewrite your logic like this:

fieldcount = 0
While fieldcount < file.Fields.Count

With file.Fields(fieldcount)

Select Case .Type
Case dbText, dbMemo, dbChar
aField = Trim(.Value)
Case Else

aField = .Value
End Select

activity.Fields(fieldcount) = aField
fieldcount = fieldcount + 1

Wend

 

[Maury Markowitz]

.... (not a Set statement) aField will be holding only the value of
file.Fields(fieldcount), not a reference to the Field object itself.

Ahhh, ok.

Therefore aField won't have a Type property. You could use the VarType
function to check the type of aField, or you could check the Type
property of file.Fields(fieldcount).

Got it. This worked like a champ, thanks!

Maury