data validation

[alex]

Hello,

I'm looking for a data validation rule that would preclude users from
entering anything into a table except:

A 13 character string that can only contain numbers or letters.
or
An 8 character string that can only contain numbers or letters.

Thanks,

alex

 

[Allen Browne]

The rule will be something like this:

(Is Null) OR (((LIKE "????????") OR (LIKE "?????????????"))
AND NOT (Like "*[!a-z]*") AND NOT LIKE "*[!0-9]*"))

That's untested, but hopefully enough to get you on the track.

 

[alex]

The rule will be something like this:

(Is Null) OR (((LIKE "????????") OR (LIKE "?????????????"))
AND NOT (Like "*[!a-z]*") AND NOT LIKE "*[!0-9]*"))

That's untested, but hopefully enough to get you on the track.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I'm looking for a data validation rule that would preclude users from
entering anything into a table except:

A 13 character string that can only contain numbers or letters.
or
An 8 character string that can only contain numbers or letters.

alex- Hide quoted text -

- Show quoted text -

Thanks Allen for the help.

I think I understand "?", which will limit the string to the same
number of characters (in my example 13 or 9 characters).
I also do not want anyone to enter a character except a letter or
number, e.g., no special characters, which I'm assuming (Like "*[!a-
z]*") AND NOT LIKE "*[!0-9]*")) should do.

I copied the above validation, but it doesn't seem to work...it allows
13 characters of any type...it also strangely does not allow any
string above or below 13 characters, even though we stated or (like
"?????????")

alex

 

[Allen Browne]

The ? indicates exactly one character.
Therefore the expression:
Like "????"
means it must be 4 characters.
You asked for only 13 characters, or only 8 characters.
So the expression is crafted to permit those 2 options only.
Not 6 characters, not 14, only 8 or 13 characters.

The expression:
[a-z]
permits alpha characters only.
The expression:
[!a-z]
permits non-alpha characters only.
The expression:
Like "*[!a-z]*"
matches a non-alpha character in any position in the string.
The expression:
NOT LIKE "*[!a-z]*"
means it is not allowed to have any non-alpha character anywhere in the
string.

You want to permit digits as well as letters, so the expression you need is:
NOT LIKE "*[!((a-z) or (0-9))]*"

(That's omitting the requirement that the field be exactly 8 or exactly 13
characters, as I am not clear about what you wanted there.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The rule will be something like this:

(Is Null) OR (((LIKE "????????") OR (LIKE "?????????????"))
AND NOT (Like "*[!a-z]*") AND NOT LIKE "*[!0-9]*"))

That's untested, but hopefully enough to get you on the track.

Hello,

I'm looking for a data validation rule that would preclude users from
entering anything into a table except:

A 13 character string that can only contain numbers or letters.
or
An 8 character string that can only contain numbers or letters.

alex- Hide quoted text -

- Show quoted text -

Thanks Allen for the help.

I think I understand "?", which will limit the string to the same
number of characters (in my example 13 or 9 characters).
I also do not want anyone to enter a character except a letter or
number, e.g., no special characters, which I'm assuming (Like "*[!a-
z]*") AND NOT LIKE "*[!0-9]*")) should do.

I copied the above validation, but it doesn't seem to work...it allows
13 characters of any type...it also strangely does not allow any
string above or below 13 characters, even though we stated or (like
"?????????")

alex

 

[alex]

The ? indicates exactly one character.
Therefore the expression:
Like "????"
means it must be 4 characters.
You asked for only 13 characters, or only 8 characters.
So the expression is crafted to permit those 2 options only.
Not 6 characters, not 14, only 8 or 13 characters.

The expression:
[a-z]
permits alpha characters only.
The expression:
[!a-z]
permits non-alpha characters only.
The expression:
Like "*[!a-z]*"
matches a non-alpha character in any position in the string.
The expression:
NOT LIKE "*[!a-z]*"
means it is not allowed to have any non-alpha character anywhere in the
string.

You want to permit digits as well as letters, so the expression you need is:
NOT LIKE "*[!((a-z) or (0-9))]*"

(That's omitting the requirement that the field be exactly 8 or exactly 13
characters, as I am not clear about what you wanted there.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

The rule will be something like this:
(Is Null) OR (((LIKE "????????") OR (LIKE "?????????????"))
AND NOT (Like "*[!a-z]*") AND NOT LIKE "*[!0-9]*"))
That's untested, but hopefully enough to get you on the track.

Hello,
I'm looking for a data validation rule that would preclude users from
entering anything into a table except:
A 13 character string that can only contain numbers or letters.
or
An 8 character string that can only contain numbers or letters.
Thanks,
alex- Hide quoted text -
- Show quoted text -

Thanks Allen for the help.

I think I understand "?", which will limit the string to the same
number of characters (in my example 13 or 9 characters).
I also do not want anyone to enter a character except a letter or
number, e.g., no special characters, which I'm assuming (Like "*[!a-
z]*") AND NOT LIKE "*[!0-9]*")) should do.

I copied the above validation, but it doesn't seem to work...it allows
13 characters of any type...it also strangely does not allow any
string above or below 13 characters, even though we stated or (like
"?????????")

alex- Hide quoted text -

- Show quoted text -

Thanks Allen for the explanation...I should be able to figure it out
from here.

alex

 

[Scoop]

How ironic, I am trying to do much the same thing.

Except in my case, I need to check a string in a custom function and
strip out any non a-z/0-9 characters. For example, if the string is
"O' Mally", I'd like to strip it down to "OMally", minus the space and
apostrophe.

Anyone know how I can do this?

 

[Jamie Collins]

I'm looking for a data validation rule that would preclude users from
entering anything into a table except:

A 13 character string that can only contain numbers or letters.
or
An 8 character string that can only contain numbers or letters.

Suggestion 1:

LIKE '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]
[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
OR LIKE '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-
Z]'

Suggestion 2:

LEN([field_name_here]) IN (13, 8)
AND [field_name_here] NOT LIKE '*[!0-9A-Z]*'
AND [field_name_here] NOT LIKE '%[!0-9A-Z]%'

Jamie.

--

 

[Jamie Collins]

How ironic, I am trying to do much the same thing.

Not really...

Except in my case, I need to check a string in a custom function and
strip out any non a-z/0-9 characters. For example, if the string is
"O' Mally", I'd like to strip it down to "OMally", minus the space and
apostrophe.

Anyone know how I can do this?

because this is best done in 'front end' code and in a loop,
remembering that VBA is generally case-sensitive e.g. (aircode):

For counter = 1 To Len(text)
If Mid$(UCase$(text), counter, 1) Like "[0-1A-Z]" Then
result = result & Mid$(text, counter, 1)
End If
Next

Jamie.

--