Data Type Mismatch in criteria in parsed text field RRS feed

  • Question

  • I have a query that parses a Short Text field entry, such as "MSPS-18345", into three parts: 
        School:  MSPS    ' All the letters before the "-"
        Fiscal Year: 18   ' The first two digits after the "-"
        Sequence: 345  ' Any number of digits that follow the first two

    SELECT [ProposalNum]
         , Left([ProposalNum], InStr([ProposalNum], "-") - 1)) AS School 
         , Mid([ProposalNum], InStr([ProposalNum],"-")+1,2) AS PropYear
         , Right([ProposalNum], Len([ProposalNum])-(InStr([ProposalNum],"-")+2)) AS Sequence

    So far, so good.  But when I add a where clause based on one of the parsed values
    WHEREMid([ProposalNum], InStr([ProposalNum],"-") +1,2) = "20";
    I get "
    Data Type Mismatch in criteria expression".  If I save the query in Access (without the WHERE clause and use its output as the data source for a second query and include,
    School = "20";

    I get the same "Data Type Mismatch".  It doesn't matter if I substitute the number 20 instead of the text string "20".  I get the same error.  Had I substituted a plain number for the InStr function, then everything would be find.  This does not seem logical.

    Stuart Bratesman

    Thursday, August 8, 2019 8:22 PM

All replies

  • Hi,

    most of the time the cause are empty fields. nz() could help:

    WHERE Mid([ProposalNum], InStr(nz([ProposalNum]),"-") +1,2) = "20"


    Thursday, August 8, 2019 10:36 PM
  • Most likely, one or more records have some unexpected content. Try running this query:

    SELECT [ProposalNum]
    WHERE [ProposalNum] Not Like "????-?????";

    Gustav Brock

    Friday, August 9, 2019 9:45 AM
  • This is something of a 'road to Dublin' question.  The real answer would be to correct the table design.  Each column in a table should be atomic, i.e. should contain a legitimate value of one attribute only.  You can easily use your expressions to insert values into new School, PropYear and Sequence columns in the table, following which you can delete the original column.  If the column is a structured key, you can then make the three columns a composite key.

    With the corrected design you'd have no problems querying the table, which would now satisfy Codd's Rule #2, The Guaranteed Access Rule, whose definition is:

    'Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.'

    Ken Sheridan, Stafford, England

    Friday, August 9, 2019 5:08 PM
  • you state that you have saved the query in Access (without the WHERE clause;  so run that select query and then inspect the mid column; do a sort A-Z and Z-A and check out the content of the data.

    when you enter a criteria into this query object i.e. 20 - - if it is a text field it will add the " " but not if it is a number field.....

    I would enter that criteria of 20 and then run it so see the results.....  ultimately then I would look at the query object in SQL view to see the syntax that is being used.....

    Saturday, August 10, 2019 1:13 PM