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
 
No, you are not understanding. Shortly after I run the query, I get an error message pop-up. When that happens I cannot access the query; I just get one screen's worth of data and I cannot scroll down to see the remaining records produced. The data mismatch error messages gives me two choices: OK or Help. Help does not provide the answer and when I click on OK, every single field in every single record (visible) turns into #Name?.

When I tried omitting >1 from the Criteria Line for the function, I get a screen with correct data that I can scroll but it includes all 750,000+ records, including those without a 1 in them. If I try to filter them to show only those containing 1 or more, I get the data mismatch error again. I then tried to save the query's output as text so I could transfer it to Excel and then filter out all those records that don't have 1's. That does not work because I again get the data mismatch error message.

Sample Data:
FIELD: gameID pitch_seq Expr1: CountCharacterInString([pitch_seq],1)
TABLE: retro10
gameID pitch_seq
ANA201004050 B1FB1B+1>S 3
ANA201004050 B1FB1B+1>S.X 3
ANA201004050 FX 0
ANA201004050 X 0
ANA201004050 X 0
ANA201004050 CBFFFX 0
ANA201004050 B11BX 2
ANA201004050 *BBF1SB>F>B 1

I hand-entered the results from Expr 1 (and this is without anything in the Criteria Line).

This is the SQL (with >1 in the Criteria Line):
SELECT Retro10.gameID, Retro10.pitch_seq, CountCharacterInString([pitch_seq],1) AS Expr1
FROM Retro10
WHERE (((CountCharacterInString([pitch_seq],1))>1));

And this is what I get when I click OK on the error message:
gameID pitch_seq Expr1
#Name? #Name?
#Name? #Name?
#Name? #Name?
#Name? #Name?
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I thought it was extra + signs you were looking for.

In this part of the SQL:
Code:
WHERE (((CountCharacterInString([pitch_seq],1))>1));

Would be:
Code:
WHERE (((CountCharacterInString([pitch_seq],"+"))>1));

But, either way, you need quotation marks around what your looking for, "+" or "1".

You shouldn't need to hand enter anything.

Try this and see how it goes.
 
Upvote 0
If I use this SQL statement to count +'s I get an immediate Data Type Mismatch error message with no data returned.

SELECT Retro10.gameID, Retro10.pitch_seq, CountCharacterInString([pitch_seq],"+") AS Expr1
FROM Retro10
WHERE (((CountCharacterInString([pitch_seq],"+"))>1));


If I use this one to count 1's, I get the correct data, followed by a Data Type Mismatch error message.

SELECT Retro10.gameID, Retro10.pitch_seq, CountCharacterInString([pitch_seq],"1") AS Expr1
FROM Retro10
WHERE (((CountCharacterInString([pitch_seq],"1"))>1));

In both cases I used >1 in the Criteria Line.

However, what I am trying to count is this number of times the pitcher throws to a base during a pitch sequence and the number of times the catcher does so. This can vary from none to several times and can be to first, second or third base. The differentiation between catcher and pitcher is made by having a + sign precede the base number for the catcher, as in:

+1/+2/+3 will be catcher's throws to 1st, 2nd, 3rd respectively while 1/2/3 will be the pitcher's throws to those bases. I am assuming that if I get this function to work I can use it to count all +1's, etc. Then, Count of 1 minus Count of +1 will equal the pitcher's throws to first base.

Where the Data Type Mismatch comes from has eluded me. I have tried various combinations of quoted and unquoted things and even tried to add the VAL function for the numbers. pitch_seq and gameID are text fields, so the numbers contained in it are text ; however, the counts developed would be numbers (and they are right-justified).
 
Upvote 0
I'm not sure about the baseball references, having never seen it. The function picks up "+1" and counts it, just as it does any other string. Your SQL looks okay.

The only thing I can think of, without seeing the database, is that the module where the function is kept, has something wrong with it.

Can you check in the Module that it's exactly like the example shows. Then go to Tools - Debug - Compile, and see if it brings up any errors.

If that doesn't come up with anything, we'll have to try something else.
 
Upvote 0
I copied and pasted the function code from your reply #4 directly to the module (and I had already checked to be certain it was the same).

Also, I do not find Debug - Compile under Tools. I have checked the database, itself, for errors and Access did not find anythinhg wrong.

I can do this by brute force, narrowing my queries and exporting the results to Excel. My text editor, Note tab Pro, will act as a paste board, allowing me to overcome Excel's limitations in that regard, and it also will let me write a regular expression parser to get the counts. It will just take a long time. What you came up with works, except that I cannot get the data out of Access. If I could, I could get the project done in an afternoon instead of working on it most of the winter. It has to be some simple thing because, as I noted, your macro is generating the write answers. Unless you come up with an "aha" moment, I'm not sure it is worth taking up any more of your time. I appreciate what you have done but even though I have had considerable computer and programming experience, I am lost in this area.

The data comes from Retrosheet Event Files and my database is made up of the 2010 through 2013 seasons under Regular Season Event Files. In order to put them into a form that can be put into a data base they must be run through BEVENT.exe (under Software Tools on that page). My database is 1.833,580 KB in size, so it is too large to send to you, although it should be possible to make a shortened comma-delimited version.

Thanks for your time and help.
 
Upvote 0
Hello again

I'll have a look at your event stuff to see what it's about. I checked out the other function in the page I linked, and it works without a module. It's just a put together formula that will go straight in as a field in your query.

Code:
Expr1: (Len([pitch_seq])-Len(Replace([pitch_seq],"+1","")))/Len("+1")

Then use >1 in the criteria line to find more than one occurrence. It should find any of your combinations.

I might even get interested in Baseball.
 
Upvote 0
I copied and pasted that code and when I try to run the query I get an error message: Undefined function 'Len' in expression.

At the moment that is the least of my worries as the table I originally was using has been corrupted by something I tried (I tried your latest expression on a identical table that covers earlier years). Fortunately, I still have the text files I developed for building the database so I can recover; however, is there some way that I can save all the queries I wrote to import back to the restored version? Is it simply enough to delete the tables and re-import them from text files without deleting the entire old database?

As for baseball, I've always liked numbers and when I grew up baseball was truly the national pastime. I started playing with baseball stats when I was 10 years old and within about four years I was even doing linear regression analysis by hand. Anyway, the Retrosheet files take some getting used to and they really should be attacked using a programming language rather than Access/Excel but I have not programmed in at least 20 years (about the time that visual programming languages came into being).

I don't know if there is a way for you to privately send me an email address from this board because I could send you a comma-delimited text file for a year's worth of play-by-play data and you could see what things are without having to go through BEVENT.exe.
 
Upvote 0
I have no idea what went on but I rebuilt the database from scratch, then compacted it. Now, Expr1: Len([pitch_seq]) works. I haven't tried any of the other things you suggested but I'll make a copy of this db and try them out, starting with Len(string)-Len(replaced string), then going on to the VBa module we started off with. That way, if one of those is causing the corruption I will know which one it is (or isn't).

I did a bunch of searching for the undefined function but I did not come across the page that you sent the link for.

What I do need to know is if there is some way to transfer all the saved queries from the original db to the new db without having to do them piecemeal. I must have 50-60 queries that I don't feel like rewriting (all the table names and fields have the same names as the original db). I'm tied up for the weekend so I won't get to things right away.

MS Access seems to be a very temperamental program and one that certainly does not lend itself to easy learning; however, I recall that around 1985 I got my first PC and I had to write my own statistical analysis program. Between that and data input, I had very little time left to actually do any analysis.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top