Force a Number Input

[yakdogs]

I am new to SQL. I have a field that I want to force atleast a 5 digit number to be input. I set it up as a null field now they are entering 0 to bypass.

Anyone have an idea of what I can do?

 

[Transcend]

How are the users entering data?

via vb, access, asp ... ??

Transcend

 

[yakdogs]

I have an access project front end.

 

[Transcend]

You'll have to edit the front end where the data entry occurs. In the field where they try to enter 0 or a number whose length is less than 5 digits you'll need some validation.

Before the 'save' happens you'll need to do a check ...

if len(textbox.text) < 5 then
msgbox(&quot;This number has to be 5 digits&quot
else
save
end if

or something like that


Transcend

 

[MeanGreen]

You could create a rule and bind it to that column on SQL Server. Example:

create table test5 (col1 varchar(5),col2 datetime)

CREATE RULE five_digit_rule
AS
@value LIKE '[0-9][0-9][0-9][0-9][0-9]'

sp_bindrule 'five_digit_rule', '[test5].[col1]'

insert test5 values ('123a5',getdate())
insert test5 values ('1235',getdate())
insert test5 values ('12345',getdate())
insert test5 values ('123a56',getdate())
insert test5 values ('12345',getdate())
insert test5 values (0,getdate())

select * from test5

Hope this helps.

 

[JamesLean]

As Transcend says, putting validation on your data entry app would be a good start.
In the database, rather than create a rule (which is mainly for backwards compatibility) I would make the column data type int and use a check constraint:

ALTER TABLE tablename
ADD CONSTRAINT fivedigits CHECK (columnname > 9999)

If you want to use a varchar column (ie if numbers can start with zeros) then use:

ALTER TABLE tablename
ADD CONSTRAINT fivedigits CHECK (columnname LIKE '[0-9][0-9][0-9][0-9][0-9]')

--James