ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> Numeric Value Out Of Range
http://www.istechforum.com/YaBB.pl?num=1147100421

Message started by rmh148 on 05/08/06 at 07:00:21

Title: Numeric Value Out Of Range
Post by rmh148 on 05/08/06 at 07:00:21

Folks, I am having a problem with BKICMSTR in the ODBC/Access. The error I get is "Numeric value out of range. (#0)". As far as I can tell this only happens when I link or open BKICMSTR. My early research leads me to believe that the Pervasive ODBC to Access is having a problem identifing the field size of "usmallint" when it converts it. I would appreciate any assistance that you may have to offer. At this point I can not link nor import this file, but all others work fine.

Title: Re: Numeric Value Out Of Range
Post by cathyh on 05/08/06 at 10:27:27

I can run Access with ODBC connection and I can access BKICMSTR with no problems.
What is the field "usmallint"? I don't have that field in my BKICMSTR file.

Title: Re: Numeric Value Out Of Range
Post by rmh148 on 05/08/06 at 11:57:28

Thanks for looking at this. Usmallint is not a field name, it's the fields type/attribute. From the research that I have done, pervasive and Microsoft said that the problem appears when the ODBC trys to convert a btrieve field that has a type of "14 - unsigned binary" to an integer for Access. In the BKICMSTR there are two fields (BKIC_PROD_PMAT and BKIC_PROD_ADTR) that are defined as type "14 - Unsigned Binary" in the database. From the pervasive site, they indicate that these need to be changed to a "long" data type for the ODBC to work. In effect, the "usmallint or 14 - unsigned binary" type in the database can contain a value of 0 - 65535, and is stored as a 4 byte field. When the ODBC converts it it stores it as a 2 byte field that can accept a number from a -32768 to 32768.

You indicated that you have no problem running against the BKICMSTR, which lead me to believe that the error message is worng. It may be that the file is locked or something like that. Maybe Lynn or someone can tell me how to see if a file is locked.

Title: Re: Numeric Value Out Of Range
Post by rmh148 on 05/12/06 at 05:49:25

Just for the record! As I stated in the previous reply. The field size was the problem. Somehow, haven't isolated it yet, there were a few records that had 65535 in the BKIC_PROD_ADTR field. As soon as I set them to 0 the ODBC worked again. I think this may have been cause by our PO-K closing some old work order to clean up RNI, but not sure yet. Anyway, thanks Cathy for at least showing some interest. It's seems that nobody else gives a daaaarn.