getting a number form a string

[iankerry]

Hi All

I get an email from our website, with the subject heading:

Promoter feedback - 23232, Bosbury Parish Hall

I want to get the number and pass it over to Access to find that particular record.

First stage is to 'capture' the number. With help from others I have this so far:

out_eventid = Trim(Right(out_mail.Subject, Len(out_mail.Subject) - InStr(1, out_mail.Subject, "ID:", vbTextCompare) - 20))

I can run this line again with Trim(left), but the problem is that this part of the subject line can be different lengths.

There are two things that might help me - one is the COMMA that appears - anything after the comma (and including) I could lose - but i don't know how to do that.

The second thing is that (for the forseeaable future anyway) the length of the number will 5 digits.

Can anyone suggest what i could try next?

many thanks

 

[nanscombe]

Two functions for you. The first to create a fingerprint of numbers, letters and others from a string ...

Public Function strFingerPrint(ByVal theString As String)
Dim lngTemp1 As Long, strTemp1 As String
strFingerPrint = ""

For lngTemp1 = 1 To Len(theString)
    
    strTemp1 = LCase$(Mid$(theString, lngTemp1, 1))
    
    Select Case strTemp1
    Case "0" To "9"
        strFingerPrint = strFingerPrint & "N"
    Case "a" To "z"
        strFingerPrint = strFingerPrint & "A"
    Case Else
        strFingerPrint = strFingerPrint & "."
    End Select
Next lngTemp1
End Function

This will convert a string to a fingerprint of "A" for alphabetical character, "N" for numerics and "." for everything else.

Your example

Promoter feedback - 23232, Bosbury Parish Hall

would be returned as

AAAAAAAA.AAAAAAAA...NNNNN..AAAAAAA.AAAAAA.AAAA

All you need to do is locate the start of "NNNNN" in the string and return the next five characters, to do this ...

Public Function numFromString(ByVal theString As String)
Dim lngTemp1 As Long, strTemp1 As String
strTemp1 = strFingerPrint(theString)
lngTemp1 = InStr(strTemp1, "NNNNN")

numFromString = ""
If lngTemp1 > 0 Then numFromString = Mid$(theString, lngTemp1, 5)
End Function

I also use my strFingerprint() function to find things like dates (01/01/2012 - NN.NN.NNNN) or times (11:06 - NN.NN, 11:06:50 - NN.NN.NN) in strings.

 

[Trevor G]

I have found this which would work in an Excel spreadsheet so you could then use some extra code to pass it to Access

Function onlyDigits(s As String) As String
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '

' Initialise return string to empty '
retval = ""
' For every character in input string, copy digits to '
' return string. '
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next

' Then return the return string. '
onlyDigits = retval
End Function

And then call it using

Sub call2()
'Call using the above function
Dim myStr As String
myStr = onlyDigits(Range("A2"))
Range("B2") = myStr
End Sub

Example

Promoter feedback - 23232, Bosbury Parish Hall
returns 23232
Big fat hat size 23456, when on head
returns 23456

 

[nanscombe]

The onlyDigits() function would fail to give you what was wanted if there were any other digits in the string, ie in the name or address.

 

[iankerry]

Re: getting a number from a string

Hi

Thanks Both for your answers - i'll given them a go and see what i can do.

Is this code best put into Outlook VBA i.e. before the value gets passed to Access, or doesn't it really matter?

I'll give them a go now.

cheers

ian

 

[Brianwarnock]

I don't know anything about handling emails but in access you can extract that number with the following, is that any use to you.

num: Mid([field1],InStr([field1],"-")+2,InStr([field1],",")-InStr([field1],"-")-2)

replace field1 with your fieldname

Brian

 

[nanscombe]

I've written it as Access VBA but it should pretty generic.

 

[iankerry]

Re: getting a number from a string

Hi all

NEAR Success!!

Many thanks for your replies.

I am not a very confident programmer and have been trying the FUNCTION solutions. I kept running into error messages which are probably pretty basic to you guys - ones like Compile Error - Argument not Optional. I googled the error and spent a while try to get to the bottom of it.

Then Brians solution came in - and with a bit of tweaking worked like a dream!

So now if i hit a button in Outlook (with my cursor on the email) I get the value passed over to Access. Brill!

Once in Access I have:

Public Sub IKfilterid(ByVal out_eventID As String)

On Error GoTo ErrorMatching
    Dim database As DAO.database, record As DAO.Recordset, list As String, EventID As String
    Dim strFilter As String

    Set database = CurrentDb
        Set record = database.OpenRecordset("SELECT eventID FROM dbo_eventsflicks WHERE [eventid] = '" & out_eventID & "'", dbOpenDynaset, [dbSeeChanges])
      
    strFilter = "[eventId] = " & record.Fields(0)
    
    DoCmd.ApplyFilter , strFilter
    Exit Sub
    
ErrorMatching:
    Dim Msg, Style, title, Help, Ctxt, Response, MyString
    MsgBox "NO MATCH FOUND", vbOKOnly, "email search"    ' Define title.
    

End Sub

My only issue seems to be the syntax near on the SET RECORD line.

Can anyone spot anything for me please?