rknowledge
asked on
String Character Evaluation
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
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
if IsNumeric(Field) then...
if isDate(field) then...
if isDate(field) then...
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","Iaw o,")
becomes:
nrcester
Does this help?
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,,
becomes:
nrcester
Does this help?
Opps, the question did not soak in. Sorry for the erroneous response
When calling the function you should pass Mid(varWord,3). I forgot to mention that
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
Cheers, Andrew
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
Thanks, hope it helps you out!
eg:
varWord = "Dr?10"
If rstSList(2) Like varWord Then