473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Changing Integer to Double

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
5 6289
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
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

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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: da Vinci | last post by:
Hi Gents, This is what I am trying to do. Say you have a double or a float with the value 14.5624 for example. How could I take that variable and get the 14 into an integer variable and the...
2
by: Jon Ripley | last post by:
Hi, This function should return the integer portion of a float truncating down to -infinity. Where int64(12.34) = 12 and int64(-12.34) = -13 but int64(-12) = -12. In reality the function is...
33
by: Samuel R. Neff | last post by:
We've been doing a little experimenting and it seems VB.NET doesn't have a direct equivalent to a C# double to integer cast. Dim d as Double = 2.5# Dim i as Integer = CType(d, Integer) Is...
1
by: redpayne | last post by:
Okay, I finally got this program to run according to what the book had us build it as. Now prof wants case 2 and case 3 to prompt again for input, check input to see if it is the correct type, then...
17
by: arindam.mukerjee | last post by:
I was running code like: #include <stdio.h> int main() { printf("%f\n", 9/5); return 0; }
17
by: Ivan K. | last post by:
I am looking at some legacy code, which begins by allocating a double matrix with the dmatrix() function from NRC as follows: double **A, **augin, **augout, **aa; A = dmatrix(1,...
12
by: aaragon | last post by:
I have this scenario: several arrays for which I have their fixed values at compilation time. Now, at runtime I need to access a specific array depending on an integer but I want to avoid if and...
3
by: john | last post by:
I have a simple module which performs basic operations on plot3d files (below). I wrapped like: f2py --fcompiler=gfortran -m plot3d -c prec.f90 plot3d.f90 That seems to work fine, but i get...
8
by: DAHMB | last post by:
The following codes works when Lines 2 and 3 are strings but when I change them to doubles it does not work. I believe my error lies in line 9 but I need help in fixing it. Thanks Dan ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.