counting dots

[Adam Milligan]

Is there a way in VBA to open a recordset, and count the number of dots in a
text field? For example a value of 1 would return 0, but a value of 1.4.2.1
would return 3. I am trying to determine at what level of a hierarchy a
particular record falls. Thanks.

Adam Milligan

 

[Dave Patrick]

Air code but something like this may help.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim i As Integer, j As Integer
i = 0
j = 0
strSQL = "SELECT Table1.Field1 FROM Table1 WHERE
(((Table1.Field2)=123)); "
rs.Open strSQL, cnn, adOpenDynamic, adLockReadOnly
For i = 1 To Len(rs!Field1)
If Mid(rs!Field1, i, 1) = Chr(46) Then j = j + 1
Next
rs.Close
cnn.Close
Set cnn = Nothing
Set rs = Nothing
MsgBox j


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Is there a way in VBA to open a recordset, and count the number of dots in
a
| text field? For example a value of 1 would return 0, but a value of
1.4.2.1
| would return 3. I am trying to determine at what level of a hierarchy a
| particular record falls. Thanks.
|
| Adam Milligan

 

[Douglas J Steele]

The number of dots in a string will be equal to:

Len(MyString) - Len(Replace(MyString, ".", "")

 

[Adam Milligan]

Thanks! I hope Mr. Patrick isn't offended if I chose to use the shorter of
the two options.

 

[Dave Patrick]

Nope, makes no never mind to me.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks! I hope Mr. Patrick isn't offended if I chose to use the shorter
of
| the two options.

 

[Robert Morley]

Technically speaking, the computer would be doing unnecessary work (the
"replace" portion) with the shorter option, but chances are that it'll make
no noticeable difference, and even if you're concerned about the speed
difference, the built-in functions may well end up executing faster than
iterating through the string with VB anyway.


Rob