By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Trim/RTrim not stripping trailing space characters in Access 97

P: n/a
I've tried using Trim or RTrim to strip trailing space characters from
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Andy, is there any chance that this text field is a fixed-length field?

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("MyFi eld").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" <co***********@yahoo.co.uk> wrote in message
news:f1*************************@posting.google.co m...
I've tried using Trim or RTrim to strip trailing space characters from
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

Nov 13 '05 #2

P: n/a
co***********@yahoo.co.uk (Andy B) wrote in message news:<f1*************************@posting.google.c om>...
I've tried using Trim or RTrim to strip trailing space characters from
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


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
Nov 13 '05 #3

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
co***********@yahoo.co.uk (Andy B) wrote in message news:<f1*************************@posting.google.c om>...
I've tried using Trim or RTrim to strip trailing space characters from
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


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


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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.