?
Solved

MS Access 2000 - remove white space when Trim() does not work

Posted on 2005-04-22
14
Medium Priority
?
464 Views
Last Modified: 2012-06-21
I want to be able to remove a leading white space.  My data has come from the internet and is in the form of a date (just the year) in an Excel spreadsheet.  I've tried using Trim() but without success.  I've had this problem before and the piece of code that worked in that context was:

sPartnerCntry = vData(n, 1)
'strip off initial space (not an ordinary space, not in all files)
If Left$(sPartnerCntry, 1) = Chr$(160) Then
      sPartnerCntry = Right$(sPartnerCntry, Len(sPartnerCntry) - 1)
End If

This is a very specific solution which only works in one context,  I would like a generalised expression which will strip any kind of white space from before or after a string.

The data I'm importing is a mixture of strings and integers.

Can anybody help?
0
Comment
Question by:maryelizabeth
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 18

Expert Comment

by:Billystyx
ID: 13841853
Have you tried LTrim(string)
and RTrim(string)

Billystyx
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13841914
<leading white space> I bet it is not really white space it is just a character that is not displayed.


there is an api that looks for alhpa and / or numeric only.
Private Declare Function IsCharAlpha Lib "User32" Alias "IsCharAlphaA" (ByVal cHar As Byte) As Long

Public Function CATrim(StringIn As String) As String
    If Not IsCharAlpha(Asc(Left$(StringIn, 1))) Then
        CATrim = Right$(StringIn, Len(StringIn) - 1)
    End If
End Function

Steve
0
 
LVL 77

Expert Comment

by:peter57r
ID: 13841953
Hi maryelizabeth,
Your problem is relatively straightforward technically but more difficult logically.
There are potentially lots of characters which don't print but which affect layout.
What you saw with your previous example was just one instance.
I would guess that you now have a different unprintable character in the first position.
You can find out what it is using  
debug.print Asc(Left(fieldname,1))

If you can say ' I never want the first character to be one with an Ascii value of more than 128', say then you can test for this as long as you are happy with the potential consequences.

Based on your existing code your revised code would be:

sPartnerCntry = vData(n, 1)
'strip off initial space (not an ordinary space, not in all files)
If asc(Left$(sPartnerCntry, 1))>128 Then
     sPartnerCntry = Right$(sPartnerCntry, Len(sPartnerCntry) - 1)
End If


Pete
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:maryelizabeth
ID: 13843200
Thank you all

Billystyx - RTrim doesn't work, I guess it's looking for the same kind of white space as Trim

Steve and Pete: Steve's solution sounded as though it might be the most satisfactory (ie least open-ended in terms of its consequences) but I can't get it to work.  I've copied and pasted the function and then used:

.....
For m = LBound(vDates, 2) To UBound(vDates, 2)
        iDate = CATrim(vDates(1, m))
        'iDate = Left(vDates(1, m), 4)
....
But I get an error: ByRef argument type mismatched and vDates is highlighted.
vDates is an array (from Excel), type is Variant, and I'm interested in one element of the array at a time.  The line that's commented out works but it didn't strike me as a very satisfactory solution.

What I'm hoping to finish up with is a Trim() type function which can clean up either end of a string.  At the moment I have one instance of white space at the beginning of some text and another where white space has started to appear at the end of a number.  I really want to be able to test all the data I'm importing in case the source data changes and more white spaces start to appear, my imports are generating c 10,000 records per import so I'm worried about slowing the process down too much
0
 

Author Comment

by:maryelizabeth
ID: 13843334
Pete
Thanks for the debug hint - the character is 160
M
0
 
LVL 77

Expert Comment

by:peter57r
ID: 13843496
Your original code should work then.  Perhaps there's more than one?

Pete
0
 

Author Comment

by:maryelizabeth
ID: 13843836
In the beginning there were none of these extra spaces.  Then one started to appear at the beginning of some country names and now one has started appearing at the end of some dates.  I wanted to make my code more robust so that it didn't matter what extra space was added, it would still work.  It's really frustrating that Trim() doesn't work in this context.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13843893
ok ... I re-wrote it to trim off all beginning and trailing non- alpha numeric characters.

Public Function CATrim(ByVal DataIn As Variant) As Variant
    Dim lngLeft As Long
    Dim lngRight As Long
   
    Dim varRet As Variant
   
    'clean up beginning (test each char left to right)
    For lngLeft = 1 To Len(DataIn & vbNullString)
        If IsCharAlpha(Asc(Mid$(DataIn, lngLeft, 1))) Then Exit For
    Next
   
    'clean up end (test each char right to left)
    For lngRight = Len(DataIn & vbNullString) To 1 Step -1
        If IsCharAlpha(Asc(Mid$(DataIn, lngRight, 1))) Then Exit For
    Next
   
    CATrim = Mid(DataIn, lngLeft, lngRight - (lngLeft - 1))
   
End Function

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13843924
last change ... remove the string specific version of Mid to make it a bit more robust in handling any type of data you throw at it. I also change the DataIn argument to be ByVal.

Public Function CATrim(ByVal DataIn As Variant) As Variant
    Dim lngLeft As Long
    Dim lngRight As Long
   
    Dim varRet As Variant
   
    'clean up beginning (test each char left to right)
    For lngLeft = 1 To Len(DataIn & vbNullString)
        If IsCharAlpha(Asc(Mid(DataIn, lngLeft, 1))) Then Exit For
    Next
   
    'clean up end (test each char right to left)
    For lngRight = Len(DataIn & vbNullString) To 1 Step -1
        If IsCharAlpha(Asc(Mid(DataIn, lngRight, 1))) Then Exit For
    Next
   
    CATrim = Mid(DataIn, lngLeft, lngRight - (lngLeft - 1))
   
End Function

Steve
0
 

Author Comment

by:maryelizabeth
ID: 13844328
Steve thanks

Now (after having put the declaration in at the top) I'm getting:
Runtime error 5
Invalid procedure call or argument
and the debugger is highlighting: CATrim = Mid(DataIn, lngLeft, lngRight - (lngLeft - 1))
CATrim = Empty
DataIn = "2002 "
lngLeft = 6
lngRight = 0


Also - having done a search for IsCharAlpha, I can see that there is also an IsCharAlphaNumeric which sounds to my inexperienced ears as though it might be more appropriate, since I'm dealing with both numbers and names.  All the characters I want at the moment are numbers.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 1000 total points
ID: 13844736
yes, you are exactly correct ... here is the complete code from my module...

Option Compare Database
Option Explicit

Private Declare Function IsCharAlphaNumeric Lib "User32" Alias "IsCharAlphaNumericA" (ByVal cHar As Byte) As Long

Public Function CATrim(ByVal DataIn As Variant) As Variant
    Dim lngLeft As Long
    Dim lngRight As Long
   
    Dim varRet As Variant
   
    'clean up beginning (test each char left to right)
    For lngLeft = 1 To Len(DataIn & vbNullString)
        If IsCharAlphaNumeric(Asc(Mid(DataIn, lngLeft, 1))) Then Exit For
    Next
   
    'clean up end (test each char right to left)
    For lngRight = Len(DataIn & vbNullString) To 1 Step -1
        If IsCharAlphaNumeric(Asc(Mid(DataIn, lngRight, 1))) Then Exit For
    Next
   
    CATrim = Mid(DataIn, lngLeft, lngRight - (lngLeft - 1))
   
End Function
0
 

Author Comment

by:maryelizabeth
ID: 13845210
Steve
Thanks - that works
That is to say, it generates no errors.
However it does produce an anomoly in the data

Year      QV      Unit      Data
2002      Quantity      KG      0
2002      Quantity      KG      24909
2002      Quantity      KG      0
2003      Quantity      KG      10
2003      Quantity      KG      25053
2003      Quantity      KG      1
2004      Quantity      KG      0
2004      Quantity      KG      37358
2004      Quantity      KG      2
0      Quantity      KG      0
0      Quantity      KG      37358
0      Quantity      KG      2

It was the dates I was having the problem with.  There should be three rows of data for each date and now I've got an extra set which duplicates the previous set except the date is set to zero - very tedious!

If, instead of using your module as follows:
iDate = CATrim(vDates(1, m))
 
I do:
iDate = Left(vDates(1, m), 4)

It doesn't happen.  And, of course, it didn't happen before the space appeared at the end of the date. Any ideas?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13845728
My function only returns a value, it does not create rows (you are doing this in Excel?) so without your complete code I have no idea of what is going on. Have you stepped through the your code to see the return value from CATrim?
0
 

Author Comment

by:maryelizabeth
ID: 13848745
A night's rest would appear to have resolved the problem.  
Everything is now working perfectly.
I'm slightly uneasy that the problem should just have disappeared and more than a little puzzled that the code, controlled by lbound/upbound on an array created outside what we were doing should appear to loop 4 times when it only had 3 elements - I shall have to watch what it does in future.

In the meantime thank you very much for your solution, I've learned some useful things.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question