Longer Integer 2 billion +

[DanBurn]

Hello,

I have a problem with a database that is full of data.

It is used to enter a reference number and then the DB generates an Autonumber as a unique ID and primary key.

the problem is the people that issue the original reference number have changed from a 9 digit number to a 10 digit number so that any numbers past 2 billion long cannot be entered.

I have tried changing the field to a text field but that creates other problems, manly the fact that now any preciding numbers in the reference are entered as well.

eg. number 0087282057 in a number field would be entered 87282057.

This creates problems in my validation of unique codes (we do get duplicates in that need to be identified.)

So the question is there a way of having larger numbers in access?
or
Any one got an Idea how to get around this?

 

[namliam]

Not sure.... but have you tried double instead of long?

Otherwize, maybe you can use a 'after update' event of the dataentry form to delete any leading zero's thus forcing the text field to act as a number field. This then allows you to easily check for duplicates.

Good luck!

 

[DanBurn]

Cheers for the help....

Ive just realised though that you can just set the number field to decimal and then set the size of the field.. durrrrrgh! lol

 

[LPurvis]

I agree with Mailman. If this reference number is not your Autonumber PK then your other field type is free to be changed to a more scalable type.
The floating point nature of Single and Double have put some unnecessary fear into some folks - but the accuracy of Double should be perfectly suitable for your needs.
If it's a worry then consider using Currency (bearing in mind that Access will occasionally attempt to display such data into a monetary format for you - it is stored purely as a numeric value though).

Decimal is an alternative too - though it has known issues (which is more dissappointing that Doubles limitations as they're expected and well documented).

 

[LPurvis]

Hadn't seen your interim post earlier.
Note my comment about Decimal field types.
http://allenbrowne.com/bug-08.html

 

[gemma-the-husky]

out of interest the largest long int number available will be 2^32 ie 4 bytes

or 65536 squared (less 1 to be pedantic)

this is about 4billion, but because you can have positive and negative numbers you get

-2billion to +2billion as valid longints.

--------
2 billion unique references seems a lot -

now offhand I can think of a few ways of achieving the desired result

a) add a new autonumber key, and change this field to a longer text field. I don't understand the point you are making about the leading zeroes - they are easier ot handle as text, not harder, I wou;ld have thought

b) change the field to text, and use that as the key

c) change the type of the field to double, and use it as the key

[access help]
Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486232E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The type-declaration character for Double is the number sign (#).

not sure what this corresponds to in terms of the absolute maxint it can handle, but i suspect its much larger that a longint

d) convince the users they dont realy need 10 digit numbers!


apart from the 4th option, any of these methods is probably going to involve you in a fair bit of coding etc, to make sure your system still works, so you need to think about the best way to achieve the desired result

 

[Banana]

Just to point out another option:

You can write your own custom data type for VBA. Of course, you cannot alter JET's datatype, but suppose you used JET's Text, then referenced a function to convert it into a 64-bit integer and used that custom data type when dealing with forms so the conversion between a text and a big integer is transparent.

Not easier than just using a decimal or double, but that's another way to do it.

 

[DanBurn]

Cheers for all the help i think im goning to stick with the decimal option.