Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Invalid number error 1

Status
Not open for further replies.

Rajesh99

Programmer
Mar 17, 2003
43
US
I run this query in access pointing to Oracle
SELECT CInt(Right(Trim(mse_name),4))
FROM db_own
WHERE ((Left([mse_name],4))='DB19') and
len(trim(mse_name)) = 8 and
CInt(Right(Trim(mse_name),4)) > 70


I get Invalid Number error. It is the where 'CInt(Right(Trim(mse_name),4)) > 70' giving problem. If I delete above line in where clause it works fine. I am connecting to Oracle db
to run this query. I though cint does convert to number. Why is this error coming up I have only numbers there ...
 
Are you sure that all Trim(mse_name) end with 4 digits ?
If not you may consider to use IsNumeric and perhaps Iif.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Yes, I did check it has numeric value. Actuall I have only 10 records that are returned they area all like 7000..7010.
The code works fine, it is only in the where cluase when I do cint at the lst line it fails. I sthere a work arround I spent almost 3 hours fixing it and can not!
 
I have only 10 records that are returned
Are you sure the other Trim(mse_name) end with 4 digits ?


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
There is only one column mse_name in tha table and it has only 10 records [DB197000 .. DB197010]... so last 4 bytes are always numbers.
 
You may consider 2 query, like this:
Query1:
SELECT CInt(Right(Trim(mse_name),4)) AS myNum
FROM db_own
WHERE ((Left([mse_name],4))='DB19') and
len(trim(mse_name)) = 8
Query2:
SELECT myNum
FROM Query1
WHERE myNum > 70

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Thanks for your replies. I got the same error. I created query1 that works fine and when I say select * from query1 it shows all results(10 records). Now when I say select * from query1 where mynum > 70 .. I get same error "Invalid number'.


Is it happening because my backend is Oracle? This works fine when backend was sqlany. Can I type cast to force it like integer that will prevet errors.
 
Hi Rajesh99,

It seems certain that it is to do with Oracle. My Oracle knowledge is rusty and I don't know where all the functions get evaluated so just some observations really.

In the 2-query version, you say the first Query works. If the second query doesn't it must be because the values are not numeric (or not being treated as numeric). I think it follows from this that the CInt function is not converting them. You might find it works if you try converting them explicitly on the second query ...

[blue][tt]SELECT myNum
FROM Query1
WHERE CInt(myNum) > 70[/tt][/blue]

This leaves the question of WHY they're not being converted. There is an Oracle conversion function TO_NUMBER (I think) which might be worth investigating. You might even try letting Oracle decide for itself without using any explicit conversion function. Another possible option, in Access, is the Val function.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top