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."
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."
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" & "*";
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I would like to count the number of times a string appears in a field for a different record but same table.
I tried the Query you recommended and it said that WHERE was misspelled.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" & "*";
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] & "*'";
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks! it worked!
You have the query filtered for only the "Storage" records so it counts only those records. Only one of them is in "Storage".
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.