What function returns the number of occurences of one string in a.

[Guest]

Within a query, I'm looking for the function (should it exist) that returns
the number of occurences a particular string appears within another string.
Akin to InStr, but returns the quantity rather than the location.

 

[Allen Browne]

AFAIK, there's no function to do that.

How about something like this:

(Len([Field1]) - Len(Replace([Field1], "What2Find", ""))) /
Len("What2Find")

 

[Guest]

There is no such function. You'll need to code it yourself. What would it
return when looking for the number of occurences of 'aa' in 'aaa'?

-Dorian

 

[Guest]

I would say 2; the first set of "aa" and the last set of "aa" within "aaa".

 

[Guest]

Thanks for your quick response. I haven't tried it yet but mathematically it
seems to work. I wasn't aware that Replace could return a copy of the string
without the sought-for expression. Brilliant and thanks!

--
Systems Administrator
AFP Imaging Corp.
Elmsford, NY

AFAIK, there's no function to do that.

How about something like this:

(Len([Field1]) - Len(Replace([Field1], "What2Find", ""))) /
Len("What2Find")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Within a query, I'm looking for the function (should it exist) that
returns
the number of occurences a particular string appears within another
string.
Akin to InStr, but returns the quantity rather than the location.

--
Steve Kraus
Systems Administrator
AFP Imaging Corp.
Elmsford, NY

 

[Guest]

My search criteria is simply a single space, hence no division necessary at
the end but noted for future use.

If the following is added an an expression in Field:
Expr1:Len([foo])-Len(Replace([foo]," ",""))
the results are 100% accurate, showing the number of spaces in [foo] for
each record.

If, however, it is added as a condition:
Len([foo])-Len(Replace([foo]," ",""))>"1"
I get a "Data type mismatch in criteria expression" error and no records are
returned save for the usual "#Name?" error.

To troubleshoot, I tried Len([foo])>"1" by itself and all of the records
whose length is greater than 1 returned as expected.

Interestingly, though, Len(Replace([Doctor]," ",""))="1" resulted in the
aforementioned error and returned no results.

Any ideas? I've tried Val(Len(Replace...) to no avail.

 

[Marshall Barton]

Within a query, I'm looking for the function (should it exist) that returns
the number of occurences a particular string appears within another string.
Akin to InStr, but returns the quantity rather than the location.

Try this expression:

(Len(s) - Len(Replace(s, q, ""))) / Len(q)

 

[Guest]

I did do that, however Access 2007 seems to like adding the quotes around the
1.



--
Systems Administrator
AFP Imaging Corp.
Elmsford, NY

My search criteria is simply a single space, hence no division necessary at
the end but noted for future use.

If the following is added an an expression in Field:
Expr1:Len([foo])-Len(Replace([foo]," ",""))
the results are 100% accurate, showing the number of spaces in [foo] for
each record.

If, however, it is added as a condition:
Len([foo])-Len(Replace([foo]," ",""))>"1"
I get a "Data type mismatch in criteria expression" error and no records are
returned save for the usual "#Name?" error.

To troubleshoot, I tried Len([foo])>"1" by itself and all of the records
whose length is greater than 1 returned as expected.

Interestingly, though, Len(Replace([Doctor]," ",""))="1" resulted in the
aforementioned error and returned no results.

The problem is simply that the value on the left of the > is
a number and you are trying to compare it to the string "1".

Change your expression to:

Len([foo])-Len(Replace([foo]," ","")) > 1

 

[Marshall Barton]

My search criteria is simply a single space, hence no division necessary at
the end but noted for future use.

If the following is added an an expression in Field:
Expr1:Len([foo])-Len(Replace([foo]," ",""))
the results are 100% accurate, showing the number of spaces in [foo] for
each record.

If, however, it is added as a condition:
Len([foo])-Len(Replace([foo]," ",""))>"1"
I get a "Data type mismatch in criteria expression" error and no records are
returned save for the usual "#Name?" error.

To troubleshoot, I tried Len([foo])>"1" by itself and all of the records
whose length is greater than 1 returned as expected.

Interestingly, though, Len(Replace([Doctor]," ",""))="1" resulted in the
aforementioned error and returned no results.

The problem is simply that the value on the left of the > is
a number and you are trying to compare it to the string "1".

Change your expression to:

Len([foo])-Len(Replace([foo]," ","")) > 1

 

[Marshall Barton]

I did do that, however Access 2007 seems to like adding the quotes around the
1.

I would consider it a bug if it did that.

Try editing and saving the query in SQL view without
switching back to the query design grid.