Validation on length check

  • Thread starter Thread starter Liz J
  • Start date Start date
L

Liz J

Hi

I have a database table that has a field to keep phone numbers. I have set
it to text and want to put validation on it so that the length of the text
inserted is either 11 characters long or 12.

I want to put it into the validation option in the table design view.
Something along the lines of

Len between 11 and 12

but that doesn't work it brings up an error when anything is put in.

Can anyone help with the syntax? I don't want to use VBA as this is for
GCSE students.

thanks

Liz
 
Maybe something like:
Like "???????????" Or Like "????????????"

? represents any single character. Replace it with # if you want to enforce
numerical-only characters.

HTH,
 
Liz J said:
Hi

I have a database table that has a field to keep phone numbers. I
have set it to text and want to put validation on it so that the
length of the text inserted is either 11 characters long or 12.

I want to put it into the validation option in the table design view.
Something along the lines of

Len between 11 and 12

but that doesn't work it brings up an error when anything is put in.

Can anyone help with the syntax? I don't want to use VBA as this is
for GCSE students.
Click to expand...

You could write it as

Len([FieldName]) > 10 And Len([FieldName]) < 13

(substituting the name of the field for "FieldName"). However, I
suspect that George Nicholson's suggestion of using the Like operator
would be better. I only posted this to show how to specify something
like that.

BTW, you can achieve the maximum-length restriction just by setting the
Field Size to 12.
 
Dirk Goldgar said:
Liz J said:
Hi

I have a database table that has a field to keep phone numbers. I
have set it to text and want to put validation on it so that the
length of the text inserted is either 11 characters long or 12.

I want to put it into the validation option in the table design view.
Something along the lines of

Len between 11 and 12

but that doesn't work it brings up an error when anything is put in.

Can anyone help with the syntax? I don't want to use VBA as this is
for GCSE students.
Click to expand...

You could write it as

Len([FieldName]) > 10 And Len([FieldName]) < 13

(substituting the name of the field for "FieldName"). However, I
suspect that George Nicholson's suggestion of using the Like operator
would be better. I only posted this to show how to specify something
like that.

BTW, you can achieve the maximum-length restriction just by setting the
Field Size to 12.

--
Click to expand...

Many thanks George and Dirk.

As this is for a group of 16 year old students, I will show them both ways
and let them choose the one that suits them best.

Cheers

Liz
 
Back
Top