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

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153
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

Well-known Member
Joined
Feb 7, 2005
Messages
621
If you copy the function code:

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.
 
Last edited:

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2005
Messages
621
This site has always helped me when I've searched around, but should have come here in the first place.
 

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153

ADVERTISEMENT

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.
 
Last edited:

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153
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

Well-known Member
Joined
Feb 7, 2005
Messages
621
You shouldn't need to save it as anything else. The results should work in the Query.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,551
Messages
5,529,474
Members
409,884
Latest member
Msinmath
Top