By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,965 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,965 IT Pros & Developers. It's quick & easy.

Changing Integer to Double

P: n/a
Hi all,

Really dumb question but I don't know how to resolve it. Looked in help and
evry book I have.

I have a table where the primary key was set as an Integer and its reached
over 140K worth of records and the numbering has restarted from 1.

I realize now that I should have set it to double. Can someone please advise
how I can save my existing records and restart the numbering from say
150,000.

Thanks all

Feb 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Before you make this change, you should be aware that floating point numbers
such as the Double are not suitable for a primary key field.

Just as 1/3 can never be represented as a decimal number, most fractional
numbers cannot be accurately represented as a floating point binary number,
so any attempt to match fields based on floating point values is going to
give you serious problems, such as records that do not match even though all
the visible digits are the same.

Perhaps if you explain why you need fractional values, we can make
suggestions as to the best way forward. For example, if the fractional
values are for Revision number, it would be simple enough to add another
field for the revision number, and make the pair of fields the primary key.

If you are determined you need the fractional values, and 4 decimal places
are enough, you could use the Currency type. Currency is a fixed-point
number, so it does not suffer from the floating point problems.

If you need to do that programmatically in Access 2000 or later:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyID CURRENCY;"
DBEngine(0)(0).Execute strSql, dbFailOnError

(I'm guessing that the current primary key field is either an AutoNumber or
a Long Integer, as you would not get 150k records into a table with an
Integer p.k.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...

Really dumb question but I don't know how to resolve it. Looked in help
and
evry book I have.

I have a table where the primary key was set as an Integer and its reached
over 140K worth of records and the numbering has restarted from 1.

I realize now that I should have set it to double. Can someone please
advise
how I can save my existing records and restart the numbering from say
150,000.

Thanks all

Feb 14 '06 #2

P: n/a
Allen,

I just want to be able to sart numbering from 150,000 and retain my original
numbers. Integer seems too small, so what value should I use which will
allow me to increment in whole numbers, and how do I shift my existing
values into the new table ?

Its not an Autonumber field just plain Integer.

Cheers

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Before you make this change, you should be aware that floating point numbers such as the Double are not suitable for a primary key field.

Just as 1/3 can never be represented as a decimal number, most fractional
numbers cannot be accurately represented as a floating point binary number, so any attempt to match fields based on floating point values is going to
give you serious problems, such as records that do not match even though all the visible digits are the same.

Perhaps if you explain why you need fractional values, we can make
suggestions as to the best way forward. For example, if the fractional
values are for Revision number, it would be simple enough to add another
field for the revision number, and make the pair of fields the primary key.
If you are determined you need the fractional values, and 4 decimal places
are enough, you could use the Currency type. Currency is a fixed-point
number, so it does not suffer from the floating point problems.

If you need to do that programmatically in Access 2000 or later:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyID CURRENCY;"
DBEngine(0)(0).Execute strSql, dbFailOnError

(I'm guessing that the current primary key field is either an AutoNumber or a Long Integer, as you would not get 150k records into a table with an
Integer p.k.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...

Really dumb question but I don't know how to resolve it. Looked in help
and
evry book I have.

I have a table where the primary key was set as an Integer and its reached over 140K worth of records and the numbering has restarted from 1.

I realize now that I should have set it to double. Can someone please
advise
how I can save my existing records and restart the numbering from say
150,000.

Thanks all


Feb 14 '06 #3

P: n/a

You need to use a Long as the datatype, if you're not using an Autonumber
field then you are either filling this field programattically or manually.
Without details on how the value is assigned to the field it's a bit
difficult to tell you how to start numbering from any value.

--

Terry Kreft
"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...
Allen,

I just want to be able to sart numbering from 150,000 and retain my original numbers. Integer seems too small, so what value should I use which will
allow me to increment in whole numbers, and how do I shift my existing
values into the new table ?

Its not an Autonumber field just plain Integer.

Cheers

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Before you make this change, you should be aware that floating point

numbers
such as the Double are not suitable for a primary key field.

Just as 1/3 can never be represented as a decimal number, most fractional
numbers cannot be accurately represented as a floating point binary

number,
so any attempt to match fields based on floating point values is going to give you serious problems, such as records that do not match even though

all
the visible digits are the same.

Perhaps if you explain why you need fractional values, we can make
suggestions as to the best way forward. For example, if the fractional
values are for Revision number, it would be simple enough to add another
field for the revision number, and make the pair of fields the primary

key.

If you are determined you need the fractional values, and 4 decimal places are enough, you could use the Currency type. Currency is a fixed-point
number, so it does not suffer from the floating point problems.

If you need to do that programmatically in Access 2000 or later:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyID CURRENCY;"
DBEngine(0)(0).Execute strSql, dbFailOnError

(I'm guessing that the current primary key field is either an AutoNumber

or
a Long Integer, as you would not get 150k records into a table with an
Integer p.k.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...

Really dumb question but I don't know how to resolve it. Looked in help and
evry book I have.

I have a table where the primary key was set as an Integer and its

reached over 140K worth of records and the numbering has restarted from 1.

I realize now that I should have set it to double. Can someone please
advise
how I can save my existing records and restart the numbering from say
150,000.

Thanks all



Feb 14 '06 #4

P: n/a
Terry,

I have just rechecked my table and my PK field is Long Integer. Surely this
means my numbering can go into many millions before it runs out of
integers.?

So I am now very confused why has it started numbering at "1" again ?

The field is filled every time I open a form called "New Items"
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:0d********************@karoo.co.uk...

You need to use a Long as the datatype, if you're not using an Autonumber
field then you are either filling this field programattically or manually.
Without details on how the value is assigned to the field it's a bit
difficult to tell you how to start numbering from any value.

--

Terry Kreft
"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...
Allen,

I just want to be able to sart numbering from 150,000 and retain my

original
numbers. Integer seems too small, so what value should I use which will
allow me to increment in whole numbers, and how do I shift my existing
values into the new table ?

Its not an Autonumber field just plain Integer.

Cheers

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Before you make this change, you should be aware that floating point

numbers
such as the Double are not suitable for a primary key field.

Just as 1/3 can never be represented as a decimal number, most fractional numbers cannot be accurately represented as a floating point binary

number,
so any attempt to match fields based on floating point values is going to give you serious problems, such as records that do not match even though
all
the visible digits are the same.

Perhaps if you explain why you need fractional values, we can make
suggestions as to the best way forward. For example, if the fractional
values are for Revision number, it would be simple enough to add
another field for the revision number, and make the pair of fields the primary

key.

If you are determined you need the fractional values, and 4 decimal places are enough, you could use the Currency type. Currency is a fixed-point
number, so it does not suffer from the floating point problems.

If you need to do that programmatically in Access 2000 or later:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyID CURRENCY;"
DBEngine(0)(0).Execute strSql, dbFailOnError

(I'm guessing that the current primary key field is either an AutoNumber or
a Long Integer, as you would not get 150k records into a table with an
Integer p.k.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...
>
> Really dumb question but I don't know how to resolve it. Looked in help > and
> evry book I have.
>
> I have a table where the primary key was set as an Integer and its

reached
> over 140K worth of records and the numbering has restarted from 1.
>
> I realize now that I should have set it to double. Can someone

please > advise
> how I can save my existing records and restart the numbering from say > 150,000.
>
> Thanks all



Feb 14 '06 #5

P: n/a
If your PK is Long but not Autonumber then you must be determining what
the next value of the index should be.

How do you decide what the next index value should be? It could be
that the effect you have seen is caused by some calculation at this
stage.

You presumably find the highest index value in use and then add 1. But
is there an implicit conversion to Int during this process?

Nonoize wrote:
Terry,

I have just rechecked my table and my PK field is Long Integer. Surely this
means my numbering can go into many millions before it runs out of
integers.?

So I am now very confused why has it started numbering at "1" again ?

The field is filled every time I open a form called "New Items"
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:0d********************@karoo.co.uk...

You need to use a Long as the datatype, if you're not using an Autonumber
field then you are either filling this field programattically or manually.
Without details on how the value is assigned to the field it's a bit
difficult to tell you how to start numbering from any value.

--

Terry Kreft
"Nonoize" <no*****@emachizit.com.au> wrote in message
news:43***********************@ken-reader.news.telstra.net...
Allen,

I just want to be able to sart numbering from 150,000 and retain my

original
numbers. Integer seems too small, so what value should I use which will
allow me to increment in whole numbers, and how do I shift my existing
values into the new table ?

Its not an Autonumber field just plain Integer.

Cheers

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
> Before you make this change, you should be aware that floating point
numbers
> such as the Double are not suitable for a primary key field.
>
> Just as 1/3 can never be represented as a decimal number, most

fractional
> numbers cannot be accurately represented as a floating point binary
number,
> so any attempt to match fields based on floating point values is going

to
> give you serious problems, such as records that do not match even though all
> the visible digits are the same.
>
> Perhaps if you explain why you need fractional values, we can make
> suggestions as to the best way forward. For example, if the fractional
> values are for Revision number, it would be simple enough to add another > field for the revision number, and make the pair of fields the primary
key.
>
> If you are determined you need the fractional values, and 4 decimal

places
> are enough, you could use the Currency type. Currency is a fixed-point
> number, so it does not suffer from the floating point problems.
>
> If you need to do that programmatically in Access 2000 or later:
> Dim strSql As String
> strSql = "ALTER TABLE MyTable ALTER COLUMN MyID CURRENCY;"
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> (I'm guessing that the current primary key field is either an AutoNumber or
> a Long Integer, as you would not get 150k records into a table with an
> Integer p.k.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Nonoize" <no*****@emachizit.com.au> wrote in message
> news:43***********************@ken-reader.news.telstra.net...
> >
> > Really dumb question but I don't know how to resolve it. Looked in

help
> > and
> > evry book I have.
> >
> > I have a table where the primary key was set as an Integer and its
reached
> > over 140K worth of records and the numbering has restarted from 1.
> >
> > I realize now that I should have set it to double. Can someone please > > advise
> > how I can save my existing records and restart the numbering from say > > 150,000.
> >
> > Thanks all
>
>



Feb 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.