Long Integer field size


S

Selby

Access 2003

My field name is Invoice # which is my primary key - the data type is number
- long integer...We are now needing to increase the invoice number to 14
digits but when we try to put that in the table we get "the value you entered
isn't valid for this field" Number is larger than field size setting
permits...Can this be adjusted to accomodate a larger number?
 
Ad

Advertisements

B

BruceM

Not as an integer. Decimal or Double may be able to handle larger values,
but most likely you should be using a text value. You obviously are not
counting invoices with a number that large. If you describe how the invoice
numbering system works it may be possible to suggest a way to make it
happen.
 
K

Klatuu

You will need to change from long integer to double.
Be aware that doing so may require some formatting of the value.
You could also use a text field, but again you would need to be concerned
about justification of the value.
 
J

Jeff Boyce

Are you telling the user that s/he MUST do what you said, or inferring that
what they've done can only be what you described?

Your statement could be ambiguously interpreted.

I find it more useful to ask for clarification when I'm not certain what was
intended...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

Selby

The invoice number is not associated with the customer number in any way. I
can't change the long integer to double because it is a joining field to an
autonumber in a many to one relationship... Do I have any other options?
 
B

BruceM

You have no options to make a Long Integer field larger. An autonumber (a
type of Long Integer) is limited to the equivalent of something around 2.1
billion positive and negative, or more than 4.2 billion total. If an
autonumber is part of the join you are constrained by that "limitation".
You can join only from an autonumber to a long integer, so that the long
integer is on the many (or the one, in a one-to-one relationship) side of
the relationship.

If you were to succeed in changing the value in the Long Integer field that
is related to the autonumber field, the records would no longer be related,
so your intent is rather unclear.

Your best choice may be to keep the current relationship and fields, and add
a new field for Invoice Number. Why do you need 14 digiits? It cannot
possibly be for the count of invoices, so Invoice Number can only be based
on logic other than an incrementing number.

Again, if you describe the invoice numbering system it may be possible to
come up with a suggestion about how to manage it.
 
Ad

Advertisements

S

Selby

This particular database is for recording freight. Just recently we have had
to combine two invoices onto one bill of lading. When we record the invoices
we need to record both to the one bill of lading. Our original thought was
to just list both invoice numbers together - ex xxxxxx00xxxxxx...That was the
question concerning the 14 digits.

In a copy database I have added a new field to the table for the alternative
invoice number and added it to my form. I can add information directly into
the table but when I try to use the form I get a message saying I could not
add the information becuase it would create duplicate values in the index,
primary key or relationship. What should I be looking for in the form?
 
J

John W. Vinson

The invoice number is not associated with the customer number in any way. I
can't change the long integer to double because it is a joining field to an
autonumber in a many to one relationship... Do I have any other options?

In that case I would suggest using TWO fields - an autonumber/long integer
"under the covers" as a meaningless primary/foreign key, and a Text field
containing your invoice number. It will probably be appropriate to put a
unique Index on this field but to use the number field as the primary key.
 
J

John Spencer

Then since you can have one or more invoice numbers you might consider
adding a new table to hold the invoice number and a pointer to the bill
of lading record. Then if you ever get to the situation that requires 3
invoice numbers, you are set. Also, searching one field for an invoice
number instead of two (or more) is much more efficient in the long run.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

Keith Wilby

Steve said:
add another field named OtherInvoiceNum. When you need the new invoice
number in a form you can display it with InvoiceNum & "00" &
OtherInvoiceNum.

And what if there are three or more? Add another "Other" to yet another
field's name?
 
B

BruceM

If you can enter values directly into the table you should be able to enter
them via the form. I'm starting to understand that the main record is a
bill of lading, and that the invoices are created separately from that.
That is, the bill of lading is created from existing invoice records. If I
do not understand correctly (or even if I do), some detail about the
database structure would help. For instance, you say you added a new field
to the form. Number field? Indexed? Duplicates allowed? Does the error
message arise when you are attempting to add values to this field?

You say the invoice number is part of a relationship to another table? What
other table? How is it that the two tables are connected by invoice number?
That is, what data are stored in the two tables that the thing they have in
common is invoice number?

The question is not what you should be looking for in the form, but rather
what is needed in the way of tables and relationships.

I agree that if there is more than one invoice per bill of lading you should
use a related table, not a second invoice number field. There is no
guarantee you won't have a third invoice number some day. Sometimes it is
OK, IMHO, to risk having a structure that does not perfectly allow for extra
data. For instance, in most cases one middle initial or middle name field
is enough. For the occasional person with two or more, add both to the same
field. This is not one of those situations. You really should consider
seriously a related table.
 
Ad

Advertisements

S

Selby

Thanks so much for all of the help. I was able to make this work for us by
naming the new field Alternate Inv #...This way we can query all of the
invoice information by either invoice number and keep it related to the one
bill of lading...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top