Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    9

    Unanswered: Baffling Error: data type mismatch in criteria expression

    Hi,

    I'm getting strange behavior using Access 2013 with VBA (via ADO). The following works fine (f1 is a text field) and produces 1000's of records:

    Code:
    SELECT f1 FROM TBL WHERE f1 IS NOT NULL
    The following computed field produces 1000's of records and the values range from 0 to 6:

    Code:
    SELECT INSTR("ABCDEF",LEFT$(f1,1)) WHERE f1 IS NOT NULL ORDER BY INSTR("ABCDEF",LEFT$(f1,1))
    But, the following produces a 'data type mismatch in criteria expression' error. Why?

    Code:
    SELECT INSTR("ABCDEF",LEFT$(f1,1)) WHERE f1 IS NOT NULL AND INSTR("ABCDEF",LEFT$(f1,1))>0
    Last edited by ken18; 06-01-17 at 20:33.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    603
    Provided Answers: 32
    sometimes its not a NULL, but just "".
    filter for both.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    804
    Provided Answers: 2
    Hi
    I am not sure how youe second query works as you have not specified a table!!

    But I think this
    Code:
    SELECT INSTR("ABCDEF",LEFT$(f1,1)) WHERE f1 IS NOT NULL AND INSTR("ABCDEF",LEFT$(f1,1))>0
    should be something like this
    Code:
    SELECT INSTR("ABCDEF",LEFT$(TBL.f1,1)) AS Position FROM TBL WHERE TBL.f1 IS NOT NULL AND INSTR("ABCDEF",LEFT$(TBL.f1,1))>0;
    ??

    and if that works then it can be simplified to
    Code:
    SELECT INSTR("ABCDEF",LEFT$(TBL.f1,1)) AS Position FROM TBL WHERE INSTR("ABCDEF",LEFT$(TBL.f1,1))>0;
    as Null is not > 0 ??


    MTB

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    188
    TBL.F1 field value is Null.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  5. #5
    Join Date
    Apr 2017
    Posts
    14
    Code:
    SELECT INSTR("ABCDEF",LEFT$(TBL.f1,1)) AS Position FROM TBL WHERE TBL.f1 IS NOT NULL AND INSTR("ABCDEF",LEFT$(TBL.f1,1))>0
    I would even go a step further. Add parentheses around different elements to avoid confusion:

    Code:
    SELECT INSTR("ABCDEF",LEFT$(TBL.f1,1)) AS Position FROM TBL WHERE (TBL.f1 IS NOT NULL) AND (INSTR("ABCDEF",LEFT$(TBL.f1,1))>0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •