?
Solved

String Character Evaluation

Posted on 2003-03-19
10
Medium Priority
?
489 Views
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.

rknowledge
0
Comment
Question by:rknowledge
[X]
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
10 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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 ?

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

Expert Comment

by:1William
ID: 8167611
if IsNumeric(Field) then...
if isDate(field) then...
0
 
LVL 1

Expert Comment

by:IanWorcester
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, "")
           
           
        Next
    End If
   
    StripChars = strTemp
   
StripChars_Exit:
    Exit Function
StripChars_Err:
    Beep
    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.

StripChars("IanWaaaorces,,,ter","Iawo,")

becomes:

nrcester

Does this help?
0
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

by:1William
ID: 8167906
Opps, the question did not soak in.  Sorry for the erroneous response
0
 
LVL 1

Expert Comment

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

Expert Comment

by:TextReport
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
0
 
LVL 1

Author Comment

by:rknowledge
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?

rknowledge
0
 
LVL 41

Accepted Solution

by:
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)
Next
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.
0
 
LVL 1

Author Comment

by:rknowledge
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
        Else
            varWord = Left(varWord, Len(varWord) - 1)
        End If
        strNewAddress = ReplaceStr(strFName, varWord, rstSList(2), 2)
        blnFound = True
        GoTo SListFound


YOU GOT THE POINTS, I LIKE IT!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8176297
Thanks, hope it helps you out!
0

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