Data type mismatch in criteria expression (Acc'97 query)

Posted on 2004-11-12
Last Modified: 2008-02-01
I've query in Access 97 where one field is calculated from text field in linked text file using function which converts text field to double format.
Query runs fine (and gives ~33000 records).

But if I set criteria (e.g. >0) to filter by this field - I get error message "Data type mismatch in criteria expression" after some rows are shown on screen.

If I change criteria expression from >0 to >"0" - I get the same error message imediatelly without any rows retrieved.

I've sorted my query by this field and in first rows I see some negative numbers like -5, then some rows with 0 and in last rows - positive numbers like 23510. So it seems that there are no "non-numeric" data in this column. But still I can't enter any criteria. Even if I enter the same field name or "Is Null" as criteria, I get this error message.

P.S. I've tried to compact and repair my database, but nothing helps...

whats wrong?

Vaidis OK
p.p.s. sorry, only 125 points available... :(
Question by:vaidisok
    LVL 58

    Accepted Solution

    The most generic conversion function is Val(). Try to add a column to your query, like this:
        ThisFieldValue: Val([ThisField])

    Then use that for sorting and filtering. You should no longer have the message.
    By the way: "using function which converts text field to double format"... which one is that? CDbl()?

    Good Luck!
    LVL 34

    Expert Comment

    Its a text field, you cant do a numeric comparison


    where Val(myTextField) > 0;

    Dave :-)
    LVL 34

    Expert Comment

    just too slow :-)
    LVL 58

    Expert Comment

    Wait... you said: "in linked text file". Did you set up field  specifications for the text file? Does Access already know that this is meant to be a double? If you did, we must look elsewhere.
    LVL 1

    Author Comment

    hi harfang,
    thanks for idea to split filter to separate column. This has given other error message: "The text file specification XXX does not exist...". Maybe there is problem with link specifications. I will check this out and let you know if it was problem source.

    Regarding conversion function - I use my own function as source data has non-standard thousand and decimal separators which I need to remove/convert.

    hi flavo,
    I know that, I have tried to add different functions/calculations to my expression, e.g. 1*myfunction([myfield]) or CLng(myfunction([myfield])). Nothing helps.

    But let's try to check import specifications.

    Vaidis OK

    LVL 58

    Expert Comment

    > "I use my own function"
    Make sure your function accepts a variant as parameter. You can later check for various types:
       If IsNumeric(pvarParameter) Then...
    Then make sure the function returns a number or Null, always.
    Finally, you can help Access along by calling: MyConvertFunction(....)+0
    The +0 tells access that even if the function is untyped, the column will be numeric...

    Good Luck
    LVL 1

    Author Comment

    Thank you harfang (and flavo)

    After I've re-linked tables the error mesage changed once again... But this time it was clear where is the problem - "invalid use of null". My function has used String type variable in calculations (and as parameter). I've changed it to Variant and now everything is working fine (my function already had part for Null/empty string conversion).

    P.S. I should have accepted latest (not first) comment from harfang:
    Comment from harfang  feedback
    Date: 11/12/2004 04:10AM PST

    But anyway - points are assigned to correct person.

    Vaidis OK

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now