Trim/RTrim not stripping trailing space characters in Access 97
Andy B
my data. When I check on the transformed data space characters are
still there.
We have an address table containing two fields: BuildName and
RoadName. Both have the following properties: size 50, not indexed,
not required, allowed zero length. Some records have BuildName,
RoadName as null, some have content. No content is 50 chr long. When i
run a Len(BuildName) query any with content comes back as 50
characters long. After I trim, either through a VB6 exe I wrote or
using an Update query, the trailing spaces are still there.
Now I'm clueless why. Is there extra properties of a text data type
that I need to adjust? I'm confident my VB exe is working - i've used
it to do other string adjustments on the data - not just trim and have
verified the results.
The table contains about 800,000 records so it's not going to be
feasible to export to excel to try trims there. I've tried exporting
to SQL Server and running a Trim data transformation there with no
success. I'm no SQL Server expert so I can't be sure whether I've
performed that correctly... but the trim process should work in Access
97 regardless.
TIA,
AndyB
Allen Browne
You can't tell through the interface, but press Ctrl+G to open the Immediate
window, and enter something like this line:
? ((dbEngine(0)(0).TableDefs("MyTable").Fields("MyField").Attributes And
dbFixedField) <> 0)
Substitute the name of your table and your field. If you get a response of
True, it is a fixed length field. You need to create the table with normal
text fields, use an Append query to populate it from the other table, with
the Trim().
Fixed width fields cannot be created through the interface, but you can
create them programmatically with DAO (by setting the attribute of the
Field), with ADOX (by setting the Fixed Length property of the Column), or
with a DDL query (a CREATE TABLE statment using CHAR where you should use
TEXT).
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Andy B" <coasta...@yahoo.co.uk> wrote in message
news:f1523bf.04081...@posting.google.com...
James Fortune
It's possible that your spaces are not ordinary spaces. Repost:
From http://www.unicode.org/charts/PDF/U0000.pdf for Basic Latin, it
appears that 160 is a NBSP (No break space). It makes sense that Trim
would not delete this character. You can create a public function
TrimNBSP() that will get rid of them and then put the function in an
update query. Air code follows:
Public Function TrimNBSP(varIn As Variant) As Variant
Dim strTemp As String
Dim strChar As String
Dim lngI As Long
Dim lngLen As Long
TrimNBSP = varIn
If IsNull(varIn) Then Exit Function
If varIn = "" Then Exit Function
lngLen = Len(varIn)
strTemp = ""
For lngI = 1 To lngLen
strChar = Mid(varIn, lngI, 1)
If Asc(strChar) <> 160 Then
strTemp = strTemp & strChar
End If
Next lngI
TrimNBSP = strTemp
End Function
Then something like:
UPDATE tblManuals SET PartNum = TrimNBSP([PartNum]) WHERE
ManualID=189;
Perhaps this will help find your problem.
James A. Fortune
Andy B
Thanks Allen & James,
I'll investigate both your suggestions Monday morning. I wasn't
expecting much response on such an old version but thank you very
much. I've got a feeling the database was created through a vb
developer's program so Allen's idea might be spot on.
Cheers,
Andy