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 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
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
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
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
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 > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
by: arindam.mukerjee |
last post by:
I was running code like:
#include <stdio.h>
int main()
{
printf("%f\n", 9/5);
return 0;
}
|
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,...
|
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...
|
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...
|
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
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |