Using a query to sum the number of times a string appears in a field

[radguy]

How do I create a query to count the number of times a string appears in a field? When I try to count the number of times a string appears making the criteria the string, I get an error message that reads "data type mismatch in criteria expression."

[June7]

Depends what you mean by 'number of times a string appears in a field'.

Is this how many times a string appears in a field for the same record? This would require a VBA custom function.

Or how many records have a given string in a field? This would be an aggregate (GROUP BY) Totals query. Maybe:
SELECT Count(*) AS CountRecords WHERE [fieldname] LIKE "*" & "some text" & "*";

[radguy]

I would like to count the number of times a string appears in a field for a different record but same table.

[radguy]

Depends what you mean by 'number of times a string appears in a field'.

Is this how many times a string appears in a field for the same record? This would require a VBA custom function.

Or how many records have a given string in a field? This would be an aggregate (GROUP BY) Totals query. Maybe:
SELECT Count(*) AS CountRecords WHERE [fieldname] LIKE "*" & "some text" & "*";

I tried the Query you recommended and it said that WHERE was misspelled.

[June7]

Ooops! I forgot: FROM tablename.

I also forgot apostrophe delimiters.

The following will trigger a parameter input prompt popup.

SELECT Count(*) AS CountRecords FROM tablename WHERE [fieldname] LIKE "'*" & [enter some text] & "*'";

[radguy]

Thanks! it worked!

[radguy]

Actually it didn't work for me.

Here is the problem... I have a table with records that have the same [tool location],as you can see from the screen shot.

.

I entered what you told me to enter for the SQL:


There are 2 Blade 1s that are in storage but it only counts 1. How can I fix it?

[June7]

You have the query filtered for only the "Storage" records so it counts only those records. Only one of them is in "Storage".