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

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... :(
LVL 1
vaidisokAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flavoCommented:
Its a text field, you cant do a numeric comparison

Use

where Val(myTextField) > 0;

Dave :-)
0
flavoCommented:
just too slow :-)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:
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
vaidisokAuthor Commented:
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
harfangCommented:
> "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
vaidisokAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.