Counting Records with a certain word

[mnewton367]

So I have a subform that is basically a query of a table. I have it programmed so that when you choose a name from the dropdown box it only displays records with that name in one of three columns. I want to put something that will also display at the bottom the number of jobs completed by that person. So if you filter someone's name and it lists all the jobs they've done, I want the bottom to have a box that inputs a number which basically says 'Job A = 43', 'Job B = 24', etc. My issue is that I need it to search for 'Job A' by a single word because the job might not always be typed in the same format. So basically if the job says 'Fixed a Computer' or 'Computer was fixed' in either of two columns, I need it to search for the records that have the word 'Computer' and tell me how many records have that word.



I'm running with a dcount right now but it has to be word for word, and in the exact same phrase, to match.

[CJ_London]

use the like criteria

e.g. Jobtxt like "*Computer*"

[June7]

Why search two fields? Probably need OR operator in the DCount filter criteria.

[Missinglinq]

I have the same question as June7...why two fields? This strongly suggests that your db is not normalized.

I'm running with a dcount right now but it has to be word for word, and in the exact same phrase, to match.

The best way to insure that data is entered, using a specific format, is to provide all possible inputs via a Combobox. If there are sometimes situations which are unusual, you could have one Combobox selection that simply says 'Other,' then have a Textbox to enter the non-standard response.

Linq ;0)>

[InsuranceGuy]

While there are some design challenges that have been pointed out, you can accomplish this fairly easily. Here's the construct:

SELECT count(*) FROM [tableName] WHERE [Field1] & [Field2] & [Field3] LIKE "*SearchString*"

or

SELECT count(*) FROM [tableName]
WHERE [field1] LIKE "*SearchString*"
 OR [Field2] LIKE "*SearchString*" OR [Field3] LIKE "*SearchString*"