Validation Rule

[DalynSoft]

I have a text field that should only allow either numbers or the character "+" or the character "-".
Eg valid entries are:
12345
97
+
-

Invalid entries are:
+45
-67
12+
A87

I have tried this in the validation rule:
Is Not Null And (Like "*[0-9]*" Or Like "+" Or Like "-")
but it allows -67 etc.

I know I could right vba to validate it but am interested if there is a validation rule that will achieve it.

 

[The_Doc_Man]

FYI, the "Like" operator, when there is no wildcard in the liked string, is equivalent to "=" in behavior and "=" takes 3 characters less to type.

 

[DHookom]

I would use a little VBA code in the before or after update event of the control. You have many more options and can include comments.

 

[DalynSoft]

Thanks Duane. I will defer to practical over academic.

 

[arnelgp]

You can also use the Textbox's Change Event to remove "unwanted" chars from your textbox.
see this demo and open sample_datasheet_form form and enter some text to Field1 textbox.
see the code of Change Event of the textbox.
see also the MSendInput Module.

 

[azhar2006]

This is a thread by allen browne talking about Validation Rules

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

The important thing to understand about NULL is that it is not a value, but the absence of a value. Because it is not a value normal operators like =, <>, <, > etc. cannot be used. If you compare anything with a NULL the answer will always be NULL, neither TRUE nor FALSE. This is why IS NULL or IS NOT NULL have to be used, meaning the column position is empty of any value, or has a value.

With a column of text data type a column position might appear to be empty, but still be NOT NULL. This is because it might contain a zero-length string. This can be prevented by setting a column's AllowZeroLength property ot False (No) in table design view. To prevent a column accepting NULLs its Required property should be set to True (Yes).
Ken Sheridan