?
Solved

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

Posted on 2004-11-12
7
Medium Priority
?
785 Views
Last Modified: 2008-02-01
Hello,
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?

Thanks!
Vaidis OK
p.p.s. sorry, only 125 points available... :(
0
Comment
Question by:vaidisok
  • 3
  • 2
  • 2
7 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 375 total points
ID: 12564425
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!
0
 
LVL 34

Expert Comment

by:flavo
ID: 12564428
Its a text field, you cant do a numeric comparison

Use

where Val(myTextField) > 0;

Dave :-)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12564433
just too slow :-)
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 58

Expert Comment

by:harfang
ID: 12564516
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.
Cheers!
0
 
LVL 1

Author Comment

by:vaidisok
ID: 12564592
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

0
 
LVL 58

Expert Comment

by:harfang
ID: 12564653
> "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
0
 
LVL 1

Author Comment

by:vaidisok
ID: 12575402
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.
:)

Rg,
Vaidis OK
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 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