counting dots Adam Milligan 05.06.2006 14:31:02 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

Re: counting dots "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 05.06.2006 16:09:03 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 "Adam Milligan" wrote: | 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

Re: counting dots "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 05.06.2006 16:20:09 The number of dots in a string will be equal to: Len(MyString) - Len(Replace(MyString, ".", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Adam Milligan" <AdamMilligan[ at ]discussions.microsoft.com> wrote in message news:DD57B8E9-5D81-4DF5-BAF8-D0C00211E527[ at ]microsoft.com... [Quoted Text] > 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

Re: counting dots Adam Milligan 05.06.2006 17:16:01 Thanks! I hope Mr. Patrick isn't offended if I chose to use the shorter of the two options. "Douglas J Steele" wrote: [Quoted Text] > The number of dots in a string will be equal to: > > Len(MyString) - Len(Replace(MyString, ".", "") > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Adam Milligan" <AdamMilligan[ at ]discussions.microsoft.com> wrote in message > news:DD57B8E9-5D81-4DF5-BAF8-D0C00211E527[ at ]microsoft.com... > > 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 > > >

Re: counting dots "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 05.06.2006 17:26:16 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 "Adam Milligan" wrote: | Thanks! I hope Mr. Patrick isn't offended if I chose to use the shorter of | the two options.

Re: counting dots "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 06.06.2006 13:35:34 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 "Adam Milligan" <AdamMilligan[ at ]discussions.microsoft.com> wrote in message news:FC559671-D884-4EFB-BCEF-28029E9C5E3D[ at ]microsoft.com... [Quoted Text] > Thanks! I hope Mr. Patrick isn't offended if I chose to use the shorter > of > the two options. > > "Douglas J Steele" wrote: > >> The number of dots in a string will be equal to: >> >> Len(MyString) - Len(Replace(MyString, ".", "") >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "Adam Milligan" <AdamMilligan[ at ]discussions.microsoft.com> wrote in message >> news:DD57B8E9-5D81-4DF5-BAF8-D0C00211E527[ at ]microsoft.com... >> > 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 >> >> >>