473,703 Members | 3,129 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trim/RTrim not stripping trailing space characters in Access 97

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
3 10690
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("My Table").Fields( "MyField").Attr ibutes 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 programmaticall y 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.goog le.com...
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
co***********@y ahoo.co.uk (Andy B) wrote in message news:<f1******* *************** ***@posting.goo gle.com>...
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
ja******@oaklan d.edu (James Fortune) wrote in message news:<a6******* *************** ****@posting.go ogle.com>...
co***********@y ahoo.co.uk (Andy B) wrote in message news:<f1******* *************** ***@posting.goo gle.com>...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1848
by: Shabam | last post by:
Is there a function in dotnet that allows me to trim user input so that: 1) It gets rid of leading/trailing spaces, tabs, newlines and whatever other non-printing characters 2) Gets rid of all tabs, newlines, and whatever other non-printing characters anywhere else, preferably replacing them with one space 3) Takes any consecutive number of spaces and replace it with just one space.
4
4461
by: rajdb2 | last post by:
Hi, I am using the following sql statement SELECT rtrim(rtrim(coalesce(substr(char(v.creationdate),1,4) || '-' || substr(char(v.creationdate),6,2) || '-' || substr(char(v.creationdate),9,2) || ' ' || substr(char(v.creationdate),12,2) || ':' || substr(char(v.creationdate),15,2) || ':' || substr(char(v.creationdate),18,2), '')) ||'~' || rtrim(coalesce(substr(char(v.lastmodifieddate),1,4) || '-' ||
9
6275
by: Durgesh Sharma | last post by:
Hi All, Pleas help me .I am a starter as far as C Language is concerned . How can i Right Trim all the white spaces of a very long (2000 chars) Charecter string ( from the Right Side ) ? or how can i make a fast Right Trim Function in c,using Binary search kind of fast algorithm ? Offcourse...I can use the classical approach too. like : Start from the right most charecter of the string to the left of the
7
4222
by: Sascha Herpers | last post by:
Hi, what is the difference between the trim function and the trim String-member? As far as I see it, both return the trimmed string and leave the original string unaltered. Is any of the two faster? Is there a general rule/opinion to prefere members over functions? Thanks for any hint.
31
2902
by: rkk | last post by:
Hi, I've written a small trim function to trim away the whitespaces in a given string. It works well with solaris forte cc compiler, but on mingw/cygwin gcc it isn't. Here is the code: char *trim(char *s) { char *begin,*end; begin = s;
4
10862
by: Mintyman | last post by:
Hi, I have erronous white space at the end of my 'description' field within my 'product' table. I tried using the RTRIM function but it won't let me because it is a TEXTBLOB (text) field. Can anyone show me how to write a query that will update all my rows automatically? I'm using SQL Server 2000.
27
6862
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How do I trim whitespace - LTRIM/RTRIM/TRIM? ----------------------------------------------------------------------- Using Regular Expressions (JavaScript 1.2/JScript 4+) : String.prototype.LTrim=new Function("return this.replace(/^\\s+/,'')") String.prototype.RTrim=new Function("return this.replace(/\\s+$/,'')") String.prototype.Trim= new...
6
3007
by: =?Utf-8?B?R2Vvcmdl?= | last post by:
Hello everyone, I think in built-in C/C++ functions, there is no direct function call to trim heading and trailing space characters, right? So, we have to implement it manually by iterating the string to check character one by one. Support not using MFC.
121
5106
by: swengineer001 | last post by:
Just looking for a few eyes on this code other than my own. void TrimCString(char *str) { // Trim whitespace from beginning: size_t i = 0; size_t j; while(isspace(str)) {
0
9109
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9002
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8956
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4420
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4677
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2434
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2057
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.