Long Integer field size

[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?

 

[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.

 

[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.

 

[Steve]

Part of your Invoice# must be some kind of code specific to the customer.
Store that in your customer table and when you need to display an Invoice#
in a form or report, concatenate the static part of the invoice# in the
customer table to the sequential invoice# in the invoice table.

Steve
(e-mail address removed)

 

[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

 

[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?

 

[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.

 

[Steve]

As Bruce requested .... Please describe how the invoice numbering system
works it may be possible to suggest a way to make it happen.....

Steve

 

[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?

 

[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.

 

[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
'====================================================

 

[Steve]

Are both Invoice#s the same? If they are, you only need one InvoiceNum
field. When you need the new invoice number in a form you can display it
with InvoiceNum & "00" & InvoiceNum. If they are not, just add another field
named OtherInvoiceNum. When you need the new invoice number in a form you
can display it with InvoiceNum & "00" & OtherInvoiceNum.

Steve
(e-mail address removed)

 

[Keith Wilby]

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?

 

[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.

 

[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...