Validation Rule need help

[Guest]

Can I create a mandatory number of characters in a field when creating a table.
Table 1
Field1 date
Field2 Time
Field3 (12 digit number required by user) 123456789012

If the user does not enter a 12 digit number in field 3 they will receive an
error message and the record will not be created.

 

[Dirk Goldgar]

In

Can I create a mandatory number of characters in a field when
creating a table. Table 1
Field1 date
Field2 Time
Field3 (12 digit number required by user) 123456789012

If the user does not enter a 12 digit number in field 3 they will
receive an error message and the record will not be created.

Is this actually a number field? Or is it a text field that will hold
numeric digits?

If it's really a number, that means that the number must be greater than
or equal to 100,000,000,000. So you can set validation rule like this:

=100000000000

But that's a lot of digits to be stored in a number field, for most
non-scientific purposes.

If it's a text field, you can set a validation rule like this:

Like '############'

You can set the message to be displayed using the Validation Text
property. Note that the above rule will allow leading zeros; in fact,
it would allow all zeros: 000000000000 would be valid.

 

[Jamie Collins]

If it's a text field, you can set a validation rule like this:

Like '############'

The OP could use ANSI-92 Query Mode SQL DDL to create a fixed width
text column (NCHAR) then add a validation rule (or in this case a
CHECK constraint) to disallow all character except numerals e.g.

CREATE TABLE Test
(
text_field CHAR(12) NOT NULL,
CONSTRAINT test_text_field_invalid_characters
CHECK (text_field NOT ALIKE '%[!0-9]%')
);

I'm using the ALIKE operator to make the validation ANSI Mode Neutral
because ALIKE uses ANSI standard wildcards/patterns even in ANSI-89
('traditional') Query Mode. One problem with

Like '############'

is that ANSI-92 Query Mode can be used to (inadvertently?) insert the
literal string, '############'

Jamie.

--

 

[Dirk Goldgar]

In

The OP could use ANSI-92 Query Mode SQL DDL to create a fixed width
text column (NCHAR) then add a validation rule (or in this case a
CHECK constraint) to disallow all character except numerals e.g.

CREATE TABLE Test
(
text_field CHAR(12) NOT NULL,
CONSTRAINT test_text_field_invalid_characters
CHECK (text_field NOT ALIKE '%[!0-9]%')
);

Yes, although I'd prefer a solution that can easily be defined in the
user interface without switching modes.

I'm using the ALIKE operator to make the validation ANSI Mode Neutral
because ALIKE uses ANSI standard wildcards/patterns even in ANSI-89
('traditional') Query Mode. One problem with

Like '############'

is that ANSI-92 Query Mode can be used to (inadvertently?) insert the
literal string, '############'

Your point is well taken, if a bit unlikely. If it's really crucial to
avoid such a possibility, it may make sense to use ALIKE to ensure you
know exactly what wild-card operators are in effect. So you could write
this as a validation rule:

ALike '____________' And Not Alike '%[!0-9]%'

I haven't tested it, but I think that would work, could be entered
directly in the Validation Rule property via the user interface, and
wouldn't involve switching to ANSI-92 mode to execute DDL.

 

[Jamie Collins]

ALike '____________' And Not Alike '%[!0-9]%'

I haven't tested it, but I think that would work, could be entered
directly in the Validation Rule property via the user interface, and
wouldn't involve switching to ANSI-92 mode to execute DDL.

I still think that NCHAR(12) is the way to go but if executing ANSI-92
Query Mode SQL DDL is really a problem (e.g. could use
Access.CurrentProject.Connection.Execute "<<SQL goes here>>") I think
this ANSI mode neutral version with a NVARCHAR(12) column would be
easier to understand:

LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Jamie.

--

 

[Dirk Goldgar]

In

I still think that NCHAR(12) is the way to go but if executing ANSI-92
Query Mode SQL DDL is really a problem (e.g. could use
Access.CurrentProject.Connection.Execute "<<SQL goes here>>") I think
this ANSI mode neutral version with a NVARCHAR(12) column would be
easier to understand:

LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Yes, that would be good. I was trying to avoid that long string of
range specifications.

 

[Jamie Collins]

LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Yes, that would be good. I was trying to avoid that long string of
range specifications.

Essentially then:

LEN(test_col) = 12 AND test_col NOT ALIKE '%[!0-9]%'

Better IMO to have one rule per test (to be able to give more granular
failure messages, etc) e.g. pesudo code:

CONSTRAINT test_col__width CHECK (LEN(test_col) = 12),
CONSTRAINT test_col__valid_characters CHECK (test_col NOT ALIKE '%[!
0-9]%'),

If Err.Number <> 0 Then
If InStr(Err.Description, "test_col__width") Then
MsgBox "Not enough characters. Must be exactly 12 numeric
characters.", , APP_NAME
ElseIf InStr(Err.Description, "test_col__valid_characters") Then
MsgBox "Illegal characters. Must be exactly 12 numeric
characters.", , APP_NAME
Else
MsgBox "<<something else went wrong>>", , APP_NAME
End If
End If

....which brings me back to my point about making the column NCHAR(12)
in the first place and dropping the check on the number of characters.

Jamie.

--