How to Return part of a string (Parse?)

[Lisab]

Given the following string, I need to write a code that would return
“MYDATABASENAME†(the part between “;DB=†and the next “;â€)
**************
ODBC;DSN=MyDSNName;SRVR=MyServerName;DB=MYDATABASENAME;UID=ID;PWD=password
**************
How would I use the InStr, Left&, Right$, and Len functions to accomplish
this?

THANKS!

 

[Jeff Boyce]

Have you looked at the examples in Access for the syntax of those functions?
Did you look up the Mid() function?

It looks to me like the string you are seeking starts in the middle of that
larger string, at character position 41, and appears to be 14 characters
long. Is that what you see?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lisab]

Unfortunately it's not that easy. The string will be different each time.
The target string will not be the same length and the contents of the string
will not be the same.

That' why I need to write code to return the porting between ";DB=" and the
next ";"

I know the Mid, Instr, Right, and the other string functions. The part I am
having trouble with is putting them together of get the results I need. (in
my last attempt, I got stuck in an endless loop)

Using the string functions have always given me a headach and I takes me
longer than necessary to figure out how to get the results I need. I was
hoping to find a solution here from one of the wonderful MVP or brilliant
Access group members

 

[Lisab]

I did it! I did it! I did it!
***********************
Dim Pos1, Pos2, StartPos, StrLen As Integer

Pos1 = InStr(strPath, ";")
Do While Pos1 <> 0
If Mid(strPath, Pos1, 4) = ";DB=" Then
StartPos = Pos1 + 4
Pos2 = InStr(Pos1 + 1, strPath, ";")
StrLen = Pos2 - StartPos
Exit Do
End If
Pos1 = InStr(Pos1 + 1, strPath, ";")
Loop

Debug.Print Mid(strPath, StartPos, StrLen)

 

[Jeff Boyce]

Thanks for posting back with your success. Now someone else sometime else
will have a way to solve the same issue!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

It might have been more efficient to use InStr to find ;DB= and then find
the next semicolon:

Function GetDBName(strPath As String) As String

Dim Pos1 As Integer
Dim Pos2 As Integer

Pos1 = InStr(strPath, ";DB=")
If Pos1 > 0 Then
' Increment Pos1 to after the equal sign
Pos1 = Pos1 + 4
' Find the next semicolon
Pos2 = InStr(Pos1, strPath, ";")
' If there isn't another semicolon, set Pos2 to
' one more than the length of the string.
' (simulates a semicolon being at the end of the string)
If Pos2 = 0 Then
Pos2 = Len(strPath) + 1
End If
GetDBName = Mid(strPath, Pos1, Pos2-Pos1)
End If

End Function

Incidentally, your declaration

Dim Pos1, Pos2, StartPos, StrLen As Integer

doesn't do what you likely think it does.

VBA doesn't allow "short circuiting" in declarations. Only StrLen is
actually being declared as an Integeros1, Pos2 and StartPos are all
variants. To make them all integers, you need either

Dim Pos1 As Integer, Pos2 As Integer, StartPos As Integer, StrLen As Integer

or

Dim Pos1 As Integer
Dim Pos2 As Integer
Dim StartPos As Integer
Dim StrLen As Integer

 

[rtviper]

Given the following string, I need to write a code that would return
“MYDATABASENAME†(the part between “;DB=†and the next “;â€)
**************
ODBC;DSN=MyDSNName;SRVR=MyServerName;DB=MYDATABASENAME;UID=ID;PWD=password
**************
How would I use the InStr, Left&, Right$, and Len functions to accomplish
this?

THANKS!

Try this
Dim strODBC, strDataName As String
Dim intStart, intlength As Integer
strODBC =
"ODBC;DSN=MyDSNName;SRVR=MyServerName;DB=AnyDataBaseName;UID=ID;PWD=password"
intStart = Val(InStr(1, strODBC, ";DB=", 0)) + 4
intlength = Val(InStr(1, strODBC, ";UID", 0)) - intStart
strDataName = Mid(strODBC, intStart, intlength)
Debug.Print strDataName

 

[Brent Spaulding \(datAdrenaline\)]

If you are using A2000 or higher you can use the Split() function in a
single line of VBA code ....

Split(Split("<your string>","DB=",,vbTextCompare)(1),";")(0)

Or .. you can use a function I created that uses the same priciple, but is
versatile in the sense that you can extract any parameter you wish:

Public Function GetParameter(ByVal strParameter As String, _
strParameterString As String, _
Optional strParameterDelimiter As String =
conAppParameterSeparator, _
Optional intCompareMethod As vbCompareMethod =
vbTextCompare) As String
'Author: Brent Spaulding
'Purpose: Return a Parameter in a Parameter string (Like a CONNECT string)
'Note: Assumes the parameter is followed by an "=" with out spaces, if the
"="
'is not there, the function will add it for you.
'-------------------------------------------------------------------------
'Returns the value of the strParameter in the strParameterString with the
parameters separated
'by a strParameterDelimiter. The method to search for strParameterDelimiter
is indicated by
'intCompareMethod.
'Common forms of parameter strings are:
'"Database=C:\MyDocs\MyDb.mdb;UID=MyUser;PWD=MyPWD"
'Usage:
'GetParameter("Datatbase=","Database=C:\MyDocs\MyDb.mdb;UID=MyUser;PWD=MyPWD",";")
'-->C:\MyDocs\MyDb.mdb

Dim strArrTemp() As String

On Error GoTo GetParameter_Error

'Massage the parameter to ensure it has and equal sign
If Right(Trim(strParameter), 1) <> "=" Then
strParameter = strParameter & "="
End If

'Split the parameter string by the parameter requested. Thus there will
be a two
'element array .. element 0 will be the left side of the parameter
string,
'(but does NOT include the strParameter since thats what Split the
string)
'element 1 will be the right side of the parameter string.
strArrTemp = Split(strParameterString, strParameter, , intCompareMethod)

'Now split the RIGHT side to get the value of the desired parameter in
element 0
If UBound(strArrTemp) > 0 Then
If Len(strArrTemp(UBound(strArrTemp))) > 0 Then
strArrTemp = Split(strArrTemp(1), strParameterDelimiter, ,
intCompareMethod)
GetParameter = strArrTemp(0)
Else
GetParameter = ""
End If
Else
GetParameter = ""
End If

On Error GoTo 0
Exit Function

GetParameter_Error:

MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure
GetParameter of Module mod_TextFunctions"

End Function