Need to extract data mid-string.

[KFox via AccessMonster.com]

Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
And here's what I'm trying to get back: Swissco\Default.htm

The number of characters from the left will always be the same, 28. And the
string will always be trimmed after the ":" (colon).

I'm struggling with how to use mid, left, right to get out what I need.

Thanks in advance!
Kellie

 

[fredg]

Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
And here's what I'm trying to get back: Swissco\Default.htm

The number of characters from the left will always be the same, 28. And the
string will always be trimmed after the ":" (colon).

I'm struggling with how to use mid, left, right to get out what I need.

Thanks in advance!
Kellie

Do it in 2 steps in a user defined function.

=Mid("\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754",29) Will
return
"Swissco\Default.htm: 754"

Then
Left("Swissco\Default.htm: 754",InStr("Swissco\Default.htm:
754",":")-1) Will return
"Swissco\Default.htm"

In a Module,:

Function ShortenString(StringIn as String) as String
Dim strNew as string
strNew = Mid(StringIn,29)
strNew = Left(strNew,InSgtr(strNew,":")-1)

ShortenString = strNew
End Function
==========

Then call it from a query:
NewColumn:ShortenString([FieldName])

or in a report or form:
= ShortenString([FieldName])

 

[KFox via AccessMonster.com]

Wow! Thank you!

I'd like to do the user defined function, but neglected to mention that the
text "\\lavaur4\PHIWEBEurope\SWAN" is different in each record. The only
thing that's constant is it's always 28 characters long.

For example:
\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
\\labaoo3\PHIWEBNASbb\WIRT_Match\Default.htm: 766

So, from those two records, I need to return:
Swissco\Default.htm
Match\Default.htm

Kellie

Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
And here's what I'm trying to get back: Swissco\Default.htm

[quoted text clipped - 6 lines]

Thanks in advance!
Kellie

Do it in 2 steps in a user defined function.

=Mid("\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754",29) Will
return
"Swissco\Default.htm: 754"

Then
Left("Swissco\Default.htm: 754",InStr("Swissco\Default.htm:
754",":")-1) Will return
"Swissco\Default.htm"

In a Module,:

Function ShortenString(StringIn as String) as String
Dim strNew as string
strNew = Mid(StringIn,29)
strNew = Left(strNew,InSgtr(strNew,":")-1)

ShortenString = strNew
End Function
==========

Then call it from a query:
NewColumn:ShortenString([FieldName])

or in a report or form:
= ShortenString([FieldName])

 

[KFox via AccessMonster.com]

Actually, I figured it out. This is what I used:

Step1: Mid([FieldName],InStr([FieldName],"_")+1,InStr([FieldName],":")-InStr(
[FieldName],"_")-1)

Thanks again for your help!!

Wow! Thank you!

I'd like to do the user defined function, but neglected to mention that the
text "\\lavaur4\PHIWEBEurope\SWAN" is different in each record. The only
thing that's constant is it's always 28 characters long.

For example:
\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
\\labaoo3\PHIWEBNASbb\WIRT_Match\Default.htm: 766

So, from those two records, I need to return:
Swissco\Default.htm
Match\Default.htm

Kellie
[quoted text clipped - 29 lines]

or in a report or form:
= ShortenString([FieldName])

 

[Guest]

Try

Mid([FieldName],29,instr([FieldName],":")-29)

 

[KFox via AccessMonster.com]

Thanks Ofer-- I'm going to write that down for future reference. No doubt,
this may come up again.

Kellie

Try

Mid([FieldName],29,instr([FieldName],":")-29)

Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
And here's what I'm trying to get back: Swissco\Default.htm

[quoted text clipped - 6 lines]

Thanks in advance!
Kellie