parse a string and insert result into new field

[BarryMK]

I've spent most of this morning playing around with parsing functions found here but sadly I’m quite hopeless at functions and am stuck so I hurl myself on your mercy again. I have a field on my form called PicFile which holds the paths to linked images such as \\server\ehealth\StrayDogs\DogPics\2007\012345.jpg and a text field called StrayRef.

I’d like to be able to parse the path data to remove this section \\server\ehealth\StrayDogs\DogPics\2007\ (which doesn't change) then remove the .jpg extension and insert the remaining number 012345 into StrayRef. One snag is that this could be a 4 or 5 figure number.

 

[Dennisk]

one way of doing this is to
1) reverse the string
2) find the 1st occurence of '.'
3) find the first occurence of '\'

this will give you all the values you require using the MID$ function to extract the text you require

 

[BarryMK]

Hi DennisK
I see the logic in your method and appreciate the heads up but the key statement I made was "but sadly I’m quite hopeless at functions" and the help in Access (if it can be called that) didn't.

I know what needs to happen and seeing your method, know that somewhat better but it's the how that I can't get my head round and I'm fed up with trying to adapt bits of code that I don't fully understand in the hope that it will work.

I'm well outside my Access comfort zone here.....

 

[Dennisk]

Gentleman bassist thoe only way to get your head round this is to suffer. I once spent 36 hours on one line of java code until I understood.

here is an example to extract a database name from its full path wich is 80% of your problem.

Public Function GetPathToFE() As String
' Extract Path from :-
'V:\SYSTEMS\NOSYSTEM.MDB
' Reversing the string and find the first occurence of '/'

Dim strReversedName As String, strName As String
Dim strChar As String * 1
Dim intI As Integer, intLength As Integer

strName = CurrentDb.Name
intLength = Len(strName)
strReversedName = ""

For intI = intLength To 1 Step -1
strChar = Mid(strName, intI, 1)
strReversedName = strReversedName & strChar
Next

intI = InStr(1, strReversedName, "\")
If intI > 0 Then
GetPathToFE = Left(strName, intLength - intI)
Else
GetPathToFE = strName
End If

End Function

it will need adapting

from an ex bass player now jazz guitarist

 

[BarryMK]

Thanks Dennis I'll have a bash with it and see what I can do.

Mmmm your fingers must be nimbler than mine - I'll stick with 4 strings thanks.

 

[Bodisathva]

Let me try to save you some coding Barry: (no offense intended, Dennis)

in a function:

    Dim charLoc as Integer
    Dim extLoc as Integer
    Dim a as String

    a = "\\server\ehealth\StrayDogs\DogPics\2007\012345.jpg"
    charLoc = (InStr(1, StrReverse(a), "\")) - 2
    extLoc = (InStr(1, StrReverse(a), ".")) - 1
    StrayRef = Mid(a, Len(a) - charLoc, charLoc - extLoc)

you could also condense the code to just define the source of the textBox as well.

 

[Dennisk]

I cant find any reference to strReverse in Access 97 only 2000 and above

 

[BarryMK]

Thankyou both for your help. I'm afraid I'm stuck with 97 for the next year at least while our IT dept decide if they can afford to upgrade 350 pc's and MS Office suites.

I guess I'll have to shelve this improvement until later.

 

[Simon_MT]

When handling images referentially it is good practice to declare the Path separately and then concatenate this with the file. There is flexibility in this approach and with 13,000 images there needs to be some rationalisation of our images rather than just one large depository.

 

[BarryMK]

When handling images referentially it is good practice to declare the Path separately and then concatenate this with the file. There is flexibility in this approach and with 13,000 images there needs to be some rationalisation of our images rather than just one large depository.

I can see the rationale for that but as I'm looking at around 200-300 image files only, storing the path as repeating data isn't an issue.

With regard to the whole parsing question, I've managed to get the image linking issue (which was the underlying problem) sorted by a complete rethink.

My thanks to everyone here, but Im still clueless about functions! My sig says it all.

 

[Simon_MT]

There is nothing wrong with 97 look at www.flowerseast.com for an Access 97 driven web site, if you want any help with scripts let me know.

Simon

 

[BarryMK]

There is nothing wrong with 97 look at www.flowerseast.com for an Access 97 driven web site, if you want any help with scripts let me know.

Simon

Access 97 is excellent but 2000 and onwards has some extras that would be desirable and we will have to upgrade at some point regardless as our IT section brings everyone here onto the same standard. Until then I work within any limitations 97 might have.

Thanks for the offer of help. I think I've cracked this improvement enough to roll it out next week. But you never know...