=========
QUESTION
———
When running a query from Microsoft Access against an Oracle table, I receive the following error and the query does not execute:

ODBC–call failed.
[Oracle][ODBC Oracle Driver][Oracle OCI]ORA-01722: invalid number. (#1722)

 

=========
ANSWER
———
You are likely doing a type conversion (from text type to number type) on a database field that has actual text in it, instead of numbers.

Example:
District_ProcLvl: Int(Right([PROCESS_LEVEL],3))

The PROCESS_LEVEL field has data like X0123

 

=========
EXAMPLES
———
Query Field:
District_ProcLvl: Int(Right([PROCESS_LEVEL],3))

The PROCESS_LEVEL table field has data like X0123, but perhaps one or two of the fields have ONLY text in them, like TEXTDATA
Then the Int function fails because it can’t convert the TEXT to a NUMBER

Try this for your Query Field instead:
District_ProcLvl: Val(Right([PROCESS_LEVEL],3))

The Val function will not fail, because it is not converting, it is simply taking any number values it can find from the PROCESS_LEVEL table field. This will result in the erroring fields, simply returning 0, and then you can focus in on which records are your problems.

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
Oracle
Query

 

=========
REF
———
http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm

 


http://www.anysitesupport.com/oracle-ora-01722-invalid-number-1722/
http://anySiteHosting.com