require 10 digits

[BillyRogers]

I have a field in a table and I want to require the user to enter a 10 digit
number. How do I do this?

Thanks

 

[Allen Browne]

Assuming a Number type field, set the validation rule as:
Between 1000000000 And 999999999

 

[BillyRogers]

I forgot to mention the field is an invoice number field with 5 leading zeros
example-0000039678 ---I need to require the user to enter the 5 leading
zeros

thanks,
Billy

 

[Allen Browne]

If you just want to *display* the leading zeros, then set the Format
property of the field to:
0000000000

If you want to *store* the leading zeros, use a Text field, with an input
mask like above.

If you want to permit alpha characters, set the Validation Rule of the field
to:
Len([Field1]) = 10
substituting your field name for "Field1".

 

[peregenem]

If you just want to *display* the leading zeros, then set the Format
property of the field to:
0000000000

If you want to *store* the leading zeros, use a Text field, with an input
mask like above.

If you want to permit alpha characters, set the Validation Rule of the field
to:
Len([Field1]) = 10
substituting your field name for "Field1".

For me this is clearly a Text column (field) fixed at 10 characters
i.e. in SQL DDL (data declaration language) that is the distinction
between a NCHAR(10) and a NVARCHAR(10). That would take care of the
length and a pattern matching CHECK constraint (Validation rule) could
be used to test for only numeric characters with the five leading zero
characters e.g.

CREATE TABLE Test (
invoice_nbr NCHAR(10) NOT NULL,
CONSTRAINT invoice_nbr_format
CHECK(invoice_nbr LIKE '00000[0-9][0-9][0-9][0-9][0-9]')
);

INSERT INTO Test VALUES ('0000012345');
-- success

INSERT INTO Test VALUES (' 12345');
-- fails

INSERT INTO Test VALUES ('1234567890');
-- fails

Or maybe the OP *did* use an Autonumber ...

 

[BillyRogers]

Yes it is a text field. Sorry I should have mentioned that.

I need it to be ten digits so it matches up to invoice numbers in another
database.(this table is to store customer credit requests). I created the
table a few months ago but have realized that people keep forgetting to enter
the leading zeros.

Eventually the invoice numbers will be 6 digits with 4 leading zeros(once we
go past invoice # 99999. The database that i'm linking to (in MySQL) has
them stored with the leading zeros.

I tried the input mask of 0000000000 and it seems to be working.
thanks for everyone's help

Billy

 

[Tim Ferguson]

I need it to be ten digits so it matches up to invoice numbers in
another database.(this table is to store customer credit requests). I
created the table a few months ago but have realized that people keep
forgetting to enter the leading zeros.

Why not be nice to your users and stick in the zeroes in the report/
query:

SELECT RIGHT(("0000000000") & InvNumString, 10) AS
FormattedInvNumString
FROM etc etc

If you are using the invoice number as a foreign key in other tables
(which is pretty likely), you may find the db works better using a Long
integer instead, and doing a Format in the output:

SELECT FORMAT(InvNumNumber,"0000000000") AS FormattedInvNumString
FROM etc etc


Hope that helps


Tim F

 

[BillyRogers]

it's a form that populates an access table. The cust serv reps enter the
invoice number ,phone number and some other info when our customers request a
refund. The inv number is the foreign key that I link to the orders table to
i can match the order info to the refund request info.

The invoice number is a text field in the MySQL table that i link to
through access to view the orders.

I haven't learned how to make field property changes in MySQL yet. I'm
probably going to take a class on MySQL next month so I can learn to make
adustments to the linked tables.

 

[peregenem]

be nice to your users and stick in the zeroes in the report/
query:

SELECT RIGHT(("0000000000") & InvNumString, 10) AS
FormattedInvNumString
FROM etc etc

Alternatively, the OP could perform a once-off data cleanup exercise
using Tim's RIGHT function solution i.e. something like:

UPDATE MyTable
SET invoice_nbr =
RIGHT$(('0000000000') & invoice_nbr, 10);

Then apply the CHECK (Validation) to ensure no more bad data gets into
this column (field).

If you are using the invoice number as a foreign key in other tables
(which is pretty likely), you may find the db works better using a Long
integer instead

I very much doubt that an INTEGER (Long) would be 'better' than an
NCHAR(10). The invoice number *is* text so best to model it as text in
the database.

 

[Tim Ferguson]

The inv number is the foreign key that I link to the orders table to
i can match the order info to the refund request info.

The invoice number is a text field in the MySQL table that i link to

.... and will almost certainly work much better as a number than a text
string.

Best of luck


Tim F

 

[peregenem]

... and will almost certainly work much better as a number than a text
string.

Tim, You have twice recommended the OP changes his database to model
fixed length text as numeric. You originally stated 'may find the db
works better', then revised to 'will almost certainly work much better'
but you didn't state your reasons. I know very little about the MySQL
platform but I find it hard to believe that an INTEGER will be 'much
better' than a CHAR(10) in terms of performance, memory, disk storage,
etc. I cannot imagine many calculations involving invoice numbers.

For me, fixed length text should be modelled as fixed length text, data
integrity being much more important than other considerations.