Re: [MS_AccessPros] Retain only the letters and numbers in a string

 

hi Steve,

thanks for testing and reporting your results! [0-9A-Z] would make sense since the brackets are substitution for a single character, so Access would know that "9" is once character and "A" is a different character.  Also, A=a, as you probably found out.  I never thought about comma being interpreted as something to look for, and apparently, neither have others that have posted examples -- nice to know! If you put a comma in a bracketed expression, it is interpreted literally ... I knew that but just never thought about it for series of lists, so thank you

have an awesome day,
crystal

On 5/26/18 10:16 AM, Steve thaw5 thaw5@suddenlink.net [MS_Access_Professionals] wrote:

Crystal,

With further testing I find that commas are included in the return string. I changed the Like to [0-9 A-Z] and found the return string did not contain commas but did contain spaces. So I tried [0-9A-Z] and neither commas nor spaces are included in the return string -- just letters and numbers.

Evidently the comma in the Like list is not considered by Access to be a delimiter, but, rather, a component of the Like list. Does that sound right to you?

Steve

On 5/25/2018 8:48 PM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

 

hi Steve,

here is a function to return letters and digits:

'~~~~~~~~~~~~~~~~~~~
Function GetLettersDigits(psString As String) As String
'180525 strive4peace
   'PARAMETERS
   '  psString is the string with information to extract
   Dim sResult As String _
      , i As Integer _
      , sChar As String * 1

   sResult = ""
   'loop through and only keep letters and digits
   For i = 1 To Len(psString)
      sChar = Mid(psString, i, 1)
      If sChar Like "[0-9,A-Z]" Then
         sResult = sResult & sChar
      End If
   Next i
   GetLettersDigits = sResult
End Function
'~~~~~~~~~~~~~~~~~~~

have an awesome day,
crystal

On 5/25/18 4:11 PM, Steve thaw5 thaw5@suddenlink.net
[MS_Access_Professionals] wrote:
> In the snippet of code below, CombinedInfo is a string containing upper
> and lower case letters, numbers, spaces and assorted special characters
> such as &, *, $, #, @, etc. I need to delete the spaces and special
> characters, retaining only the letters (upper and lower case) and
> numbers. What type of test can I use in the For-Next loop to do that?
>
>    For MyCounter = 1 To Len(CombinedInfo)
>       Debug.Print MyCounter & " " & Mid(CombinedInfo, MyCounter, 1)
>    Next MyCounter
>
> Thanks, Steve
>
>
> ------------------------------------
> Posted by: Steve thaw5 <thaw5@suddenlink.net>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___