"Find" function/expression in access

[Snorky85]

Hello,

I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.

Got a problem at the minute though and hoping someone can help. Been :banghead: for the last 60-90mins!

What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith
GISurgery20120521JDoe

I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've run out of ideas. I've tried combining the mid function with instr function but it comes back as too complex! (more likely I have done it wrong!). Any genius out there who can help me??

 

[pbaldy]

What was your effort? Those two functions working together should work, presuming you can look for the first 2 and get 8 digits from there.

 

[Isskint]

Instr works just like Find. The 'problem' you would have is knowing exactly where the date part is. Now i am assuming that field is a concantantion of 3 pieces of data (Procedure - date - patient?). If so seperate them with a delimeter (any character that is not going to come up in general use, EG - or /) then you can use instr(Model_ID,"/") to find the date start.

 

[Alansidman]

This works for me in a query

Mid([field1],InStr(1,[Field1],"20"),8)

 

[boblarson]

Here's one way.

Copy this function into a STANDARD module (not form, report, or class module, but standard):

Function GetDateVal(varInput As Variant) As String
    Dim lng As Long
    Dim strHold As String
    Dim strCompare As String
    If Not IsNull(varInput) Then
        For lng = 1 To Len(strInput)
            strCompare = Mid(strInput, lng, 1)
            If IsNumeric(strCompare) Then
                strHold = strHold & strCompare
            End If
        Next
        GetDateVal = strHold
    End If
End Function

Then you can get it a query by using

MyDate:GetDateVal([OriginalFieldNameHere])

 

[boblarson]

Boy I was slow this time...

 

[Alansidman]

@Bob
But you had to write out lines of code.

 

[Snorky85]

Hello - thanks for the responses - much appreciated.

I had done the same formula as Alan and it still says too complex. Just tried Bob's code and it processes but the result is a blank. Am I supposed to change the code so it identifies where the "2012" is as the start? If so - where do I change it?? Sorry - I am very new to VBA (I have bought the dummies guide!)

 

[boblarson]

Hello - thanks for the responses - much appreciated.

I had done the same formula as Alan and it still says too complex. Just tried Bob's code and it processes but the result is a blank. Am I supposed to change the code so it identifies where the "2012" is as the start? If so - where do I change it?? Sorry - I am very new to VBA (I have bought the dummies guide!)

Worked for me in my testing. And, no, the code shouldn't need to be changed. Did you copy it exactly as shown? How are you calling it?

 

[Alansidman]

My formula worked for me. See attached.

 

[Snorky85]

Hi bob, I tried the code exactly as you put it and did the getdateval and it runs the query but it doesn't bring back any data - the column is blank Any ideas?