A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count for multiple occurrence of an expression in a string



 
 
Thread Tools Display Modes
  #11  
Old June 26th, 2006, 04:33 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Count for multiple occurrence of an expression in a string

You are awesome. This answers my question
Thanks a lot !


"Larry Daugherty" wrote:

What comes redily to mind is a function that accepts two arguments:
the string to analyze and the comparison character. Look in Help for
Mid. The function would return an number of times the comparison
character was found in the string as an integer.

HTH
--
-Larry-
--

"chinky" wrote in message
...
I want to count number of '.' (dots) in an expression e,g.

asd.rty.de.com
(answer = 3 dots at positions 4,8,11 respectively) . Instr functions

gives
only the first occurrence.
Any help is appreciated
Thanks
Anupam




  #12  
Old June 26th, 2006, 04:35 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Count for multiple occurrence of an expression in a string

My apologies, I meant to say that I am using Access 2003
I am using it in SQL query, probably that is again my mistake.
Need guidance on how to use it.
Thanks


"chinky" wrote:

I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com
(answer = 3 dots at positions 4,8,11 respectively) . Instr functions gives
only the first occurrence.
Any help is appreciated
Thanks
Anupam

  #13  
Old June 28th, 2006, 10:01 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Count for multiple occurrence of an expression in a string


Duane Hookom wrote:
I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com
(answer = 3 dots at positions 4,8,11 respectively)


I think we need another possible solution.


As this is the tabledesign group, what about a data-driven solution?

If the OP has a Sequence table of integers (seq), parsing and counting
is easy:

SELECT T2.data_col, COUNT(S1.seq) AS tally
FROM Test2 AS T2, [Sequence] AS S1
WHERE MID$(T2.data_col, S1.seq, 1) = '.'
AND S1.seq BETWEEN 1 AND 20
GROUP BY T2.data_col

Here's the VBA to fully reproduce:

Sub CountChar()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create Sequence table seq = 1 to 100
.Execute _
"CREATE TABLE [Sequence] ( seq INTEGER NOT" & _
" NULL PRIMARY KEY)"
.Execute _
"INSERT INTO [Sequence] (seq) VALUES (1);"
.Execute _
"INSERT INTO [Sequence] SELECT Units.nbr" & _
" + Tens.nbr AS seq FROM ( SELECT nbr FROM" & _
" ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
" ALL SELECT 1 FROM [Sequence] UNION ALL" & _
" SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
" 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
" [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
" UNION ALL SELECT 6 FROM [Sequence] UNION" & _
" ALL SELECT 7 FROM [Sequence] UNION ALL" & _
" SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
" 9 FROM [Sequence] ) AS Digits ) AS Units," & _
" ( SELECT nbr * 10 AS nbr FROM ( SELECT" & _
" 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
" 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
" [Sequence] UNION ALL SELECT 3 FROM [Sequence]" & _
" UNION ALL SELECT 4 FROM [Sequence] UNION" & _
" ALL SELECT 5 FROM [Sequence] UNION ALL" & _
" SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
" 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
" [Sequence] UNION ALL SELECT 9 FROM [Sequence]" & _
" ) AS Digits ) AS Tens WHERE Units.nbr +" & _
" Tens.nbr BETWEEN 2 AND 100 "

' Create test table
.Execute _
"CREATE TABLE Test2 (data_col VARCHAR(20)" & _
" NOT NULL);"
.Execute _
"INSERT INTO Test2 (data_col) VALUES" & _
" ('asd.rty.de.com');"

' Show results parsed
Dim rs
Set rs = .Execute( _
"SELECT T2.data_col, S1.seq AS pos, MID$(T2.data_col," & _
" S1.seq, 1) AS data_char FROM Test2 AS T2," & _
" [Sequence] AS S1 WHERE MID$(T2.data_col," & _
" S1.seq, 1) = '.' AND S1.seq BETWEEN 1 AND" & _
" 20")
MsgBox rs.GetString
rs.Close

' Show results tally
Set rs = .Execute( _
"SELECT T2.data_col, COUNT(S1.seq) AS tally" & _
" FROM Test2 AS T2, [Sequence] AS S1 WHERE" & _
" MID$(T2.data_col, S1.seq, 1) = '.' AND" & _
" S1.seq BETWEEN 1 AND 20 GROUP BY T2.data_col")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Occurances of String in a Column? Bill General Discussion 4 February 23rd, 2006 01:30 AM
Print Search Listbox Results in Report Filter Preview ... HELP!!! [email protected] Setting Up & Running Reports 0 January 27th, 2006 08:34 PM
Requested string function Tom Ellison Running & Setting Up Queries 3 December 15th, 2005 07:58 PM
COUNT: Counting the number of times "X" comes up after the occurrence of "Y" DrSues02 General Discussion 5 August 3rd, 2004 06:35 AM
Tough One, trying to count occurences of string based on string in separate column RagDyer Worksheet Functions 2 February 28th, 2004 03:10 AM


All times are GMT +1. The time now is 03:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.