String Character Evaluation

Posted on 2003-03-19
Medium Priority
Last Modified: 2012-05-04
I am trying to evaluate specific character entries in a string.  Is there a function to evaluate a character as alpha, numeric and other?

For instance I have the following lines of code where:

varWord = "Dr-10"
rstSList(2)= "Dr"

ElseIf InStr(varWord, rstSList(2)) Then
    lngPos = Len(rstSList(2))

What I need to do is evaluate the characters to the right of the "Dr" entry.  Part of the result needs to parse the varWord entry, but the rest needs to remove the non-alpha and non-numeric characters.  As a further example:

varWord = "Dr.-10"

How can I get the result?:

Result = "Dr 10"

I've done many string parsing routines B4, but not like this one.  Any help/suggestions would be appreciated.

Question by:rknowledge
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 41

Expert Comment

ID: 8167471
You could use the Like operator. Are you trying to retrieve the characters to the right of the Dr or just find a match ?

varWord = "Dr?10"
If rstSList(2) Like varWord Then
LVL 18

Expert Comment

ID: 8167611
if IsNumeric(Field) then...
if isDate(field) then...

Expert Comment

ID: 8167772
Try this function :

Function StripChars(ByVal strEntryString As String, ByVal strRemoveCharacters As String) As String

    On Error GoTo StripChars_Err
    Dim intCount As Integer
    Dim strReplaceChar As String
    Dim strTemp As String
    strTemp = strEntryString
    ' Loop through all chars in RemoveCharacters string (All chars that need to be removed)
    If strRemoveCharacters > "" Then
        For intCount = 1 To Len(strRemoveCharacters)
            strReplaceChar = Mid(strRemoveCharacters, intCount, 1)
            strTemp = Replace(strTemp, strReplaceChar, "")
    End If
    StripChars = strTemp
    Exit Function
    MsgBox Error, vbExclamation
    Resume StripChars_Exit
End Function

Pass the string and the characters to be removed, and it will strip them from the string, E.g.




Does this help?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 18

Expert Comment

ID: 8167906
Opps, the question did not soak in.  Sorry for the erroneous response

Expert Comment

ID: 8167962
When calling the function you should pass Mid(varWord,3). I forgot to mention that
LVL 28

Expert Comment

ID: 8168066
It sounds like an InputMask may be the easier option for you. Also Access 2000 and later has a built in function called Replace() that allows you to replace character(s) within a string with other character(s) or an empty string.

Cheers, Andrew

Author Comment

ID: 8175263
Thanks for some of the comments.  Unfortunately these will not work for me in this case.  Let me expain a bit more.

My program has data stored on Quality Control checks for installation work done at homes and businesses.  The key field I am looking to work w/ is the address.

I already have written the address entry check that meets all the standards for a proper address as far as the first word being numeric and the second if (ie. North will transform to "N") and Drive can only be entered as DR.

All of the inspection techs use digital cameras for pictures of various aspects of the work.  They rename these pictures and store them in a folder on the network.  I have chosen to use an image field on a form to actually view the pictures and depending on the current selected record a list of the associated pictures are populated in a list box.  From here you can choose which picture to view and the image will link to the file with the same address(it's a very stringent lookup).

The problem comes in where the QC techs name their picture outside of any kind of interface where I control their entry.  I am trying to check all archived pictures and rename automatically or manually to meet the standards.

If I have the address:

123 N Elm St     in the database the pic file name may look like:

123 N. Elm St., GBB (2022).jpg

There is no telling what characters they may use.  If I could distinguish alpha characters directly my problem is solved.  Would I need to build a list of alpha characters and parse the string that way or is there some handy-dandy function?

LVL 41

Accepted Solution

shanesuebsahakarn earned 400 total points
ID: 8175361
Something like this:

Function StripAlpha(strIn As String) As String
Dim strOut As String
Dim I As Long

For I=1 To Len(strIn)
   If Instr("abcdefghijklmnopqrstuvwxyz ",Mid$(strIn,I,1))<>0 Then strOut = strOut & Mid$(strIn,I,1)
StripAlpha = strOut
End Function

You can use this to retrieve the alpha characters in a string which you can then use a Like operator to match. If you need other characters, just add them to the abcdef...etc bit.

Author Comment

ID: 8176281
You know what, that's would work.  I was thinking of something more complex.  Sometimes it helps to ask questions.

Thanks, there's some more to it but I know that's on the right track.

Anyway I did work out another solution.  I was able to leave the end of the filename string so I did this instead.

ElseIf varWord = rstSList(1) Or _
    InStr(varWord, rstSList(1)) Then
        If IsNumeric(Right(varWord, 1)) Then
            If IsNumeric(Right(varWord, 2)) Then
                varWord = Left(varWord, Len(varWord) - 2)
            End If
            varWord = Left(varWord, Len(varWord) - 1)
        End If
        strNewAddress = ReplaceStr(strFName, varWord, rstSList(2), 2)
        blnFound = True
        GoTo SListFound

LVL 41

Expert Comment

ID: 8176297
Thanks, hope it helps you out!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

765 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