Results 1 to 2 of 2
  1. #1
    ottoc is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2014
    Location
    US
    Posts
    2

    data type mismatch

    Play-by-play data from Project Retrosheet includes the pitch sequence for each batter's appearance in more recent years but the pitch sequences include a number of extraneous characters representing more than just balls, strikes and balls put into play. They also include throws made to bases by pitchers and catchers, pitches in the dirt blocked by catchers, et al.




    For example: B1C1F*B*B>X shows a ball, throw to 1st by pitcher, called strike, throw to first by pitcher, foul ball, two balls in the dirt blocked by catcher, the runner going on the pitch as the batter puts the ball into play. If I just want to count the number of pitches thrown, I need to strip out the extraneous symbols, which is what the following SQL statement does: BCFBBX. LEN[clean] is the number of pitches. The query also pairs the catcher with a given pitcher and vice versa.


    SELECT Val(Mid$([gameID],4,4)) AS [Year], Retro10.pitch_seq, Replace(Replace(Replace(Replace(Replace(Replace(Re place(Replace([pitch_seq],"N",""),"1",""),"2",""),"3",""),".",""),"*","")," +",""),">","") AS clean, Len([clean]) AS pitches, Retro10.pitcher, Retro10.catcher, [pitcher] & "_" & [catcher] AS PandC, [catcher] & "_" & [pitcher] AS CandP
    FROM Retro10;


    My problem is that I expected to write a second query to sum the number of pitches for each catcher/pitcher and pitcher/catcher pairs:


    SELECT CLEAN_pitch_seq.CandP, Sum(CLEAN_pitch_seq.pitches) AS SumOfPitches
    FROM CLEAN_pitch_seq
    GROUP BY CLEAN_pitch_seq.CandP;


    However, I get a "Data type mismatch in criteria expression" when I run this second query and I have no idea why. Similarly, if I change the query to sum PandC.


    It looked to me like a straightforward aggregate. It's a simple subtotal or SUMIF in Excel but I have over 750K records. What am I missing?

    Thanks.
    Last edited by ottoc; 12-01-2014 at 08:10 AM. Reason: removed extraneous info

  2. #2
    ottoc is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2014
    Location
    US
    Posts
    2
    Never mind, I finally found which two of my 191+ K records were fouling up the matter. I tried to export the query to Excel and got a more detailed explanation of what was causing the problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Type Mismatch
    By Mtyetti2 in forum Queries
    Replies: 3
    Last Post: 10-23-2013, 11:48 AM
  2. Data type mismatch error
    By nigelbloomy in forum Programming
    Replies: 1
    Last Post: 08-01-2012, 09:19 AM
  3. Data Type Mismatch in SQL
    By Phred in forum Queries
    Replies: 2
    Last Post: 01-04-2012, 03:40 PM
  4. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 03:48 AM
  5. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums