string manipulaton - remove unwanted characters (1 Viewer)

rolaaus

Registered User.
Local time
Today, 05:59
Joined
Feb 12, 2008
Messages
84
Hi guys, I'm hoping that someone here may have needed to accomplish what I need to do and have already written a solution, because I have already done some net searches for this an can only find an Excel solution that doesn't port over to Access VBA.

I have some data that is being imported from Excel into Access, and everything I need to have working does, except that there are unrecognized characters being imported into the Access fields (like carriage returns). I have noticed that Excel has a Clean function, it would be possible for me to use, as I already have some Automation going on between Access and Excel, but I'm wondering what would be the best ay to get rid of non-printable characters?
 

RuralGuy

AWF VIP
Local time
Today, 06:59
Joined
Jul 2, 2005
Messages
13,826
Try putting this function in a standard module named basFunctions.
Code:
Public Function Clean(InString As String) As String
'-- Returns only printable characters from InString
   Dim x As Integer
   For x = 1 To Len(InString)
      If Asc(Mid(InString, x, 1)) > 31 And Asc(Mid(InString, x, 1)) < 127 Then
         Clean = Clean & Mid(InString, x, 1)
      End If
   Next x

End Function
Then invoke it whenever you need it.
 

rolaaus

Registered User.
Local time
Today, 05:59
Joined
Feb 12, 2008
Messages
84
Looks like that did the trick- I can't remember what I was trying, but I know what I was looking at had to do with doing this via Excel.. anyways, you're code worked like a charm, thanx
 

Users who are viewing this thread

Top Bottom