Access query to find more than one random occurrence of character in a word

[baseball]

Access 2007 on Windows 7

Have a field that has words of varying lengths that are made up of letters, numbers and characters. One stipulation is that a plus sign is always followed by an integer, +1 or +2 or +3. Using the Query Wizard, I used this to pull the records that have plus signs somewhere in the word. However, if I use Like "*" & "+" & "*" the total is slightly different than if I use Like "*" & "+1" & "*", etc.

The most obvious explanation is that some of the words contains more than one plus sign. How do I write the query to find those words?

Example of typical record: *BBC+1B>S.FB

 

[Marbles]

I've tried the function in the first reply on this page:

count occurence of string within a string. | dBforums – Everything on Databases, Design, Developers and Administrators

It seems to work okay.

 

[baseball]

The function CountCharacterInString asks for (strToSearch As String, strCharToCount As String). How do I tell Access that the string I want to search is another field? In my case it would be the field PitchSeq and I want to find plus signs, so would I write it as CountCharacterInString([PitchSeq], "+")?

That question might be of the gee-doesn't-this-guy-know-anything variety but I'm not accustomed to writing one's own functions and then using them. And if I am understanding things correctly from the link you included, the string character I want to count is not limited to a single character?

 

[Marbles]

If you copy the function code:

Function CountCharacterInString(strToSearch As String, strCharToCount As String) As Long
   Dim lPos As Long
   Dim lTotal As Long
   For lPos = 1 To Len(strToSearch)
      If Mid$(strToSearch, lPos, Len(strCharToCount)) = strCharToCount Then
         lTotal = lTotal + 1
      End If
   Next
   CountCharacterInString = lTotal
End Function

From the page. Then in the database, go to to Create - New - Module. Paste the code, then save and close the Module.

After that, in the Builder in design view of the query, the function will be available to use as you have it in your post. It will be in the Functions List as one from your database.

In the Criteria line put >1 and that'll only show the extra + signs.

 

[baseball]

Thank you for taking time to answer my questions. I mainly do most of my querying in Excel but when I work with very large data sources I need to use Access. Simple queries don't give me a problem but wwhen they start to get complicated I have problems figuring out how to write them using only Access.

 

[Marbles]

This site has always helped me when I've searched around, but should have come here in the first place.

 

[baseball]

This seems to count the number of ones in each record but I also get an error pop-up window: "Data type mismatch in criteria expression" and when I click OK, every record in every field changes from its proper form to #Name?. However, if I omit the criteria, >1, I get all the records, over 750,000 of them, the great majority of which do not contain 1's. And if I try to filter for records >0, I get the same error message and #Name? change. I did try to write the criterion as <>"0", but the same thing happened.

I don't know what happens if I try to save it as an Excel or text file.

UPDATE: It won't save as a text file, either. It gives the same error message.

 

[Marbles]

Did you put >1 in the Criteria Line of the Function?

 

[baseball]

Yes, and as I said, I also tried <>"0" thanking that might take care of the type mismatch.

It is finding the records even though there is allegedly a type mismatch; however, I cannot access that data because of the type mismatch and I do no have a clue.

 

[Marbles]

You shouldn't need to save it as anything else. The results should work in the Query.