Validation rule for numbers and a specific phrase

[82412]

I have a text box in a form, into which I want to be able to enter a number between 0 and 100 or a specific two-word phrase. I also want to be able to leave the box blank.

I have used the following as the validation rule:

="No Progress" Or Between 0 And 100 Or Is Null

and I have prepared validation text. The problem is that although I am now successfully able to make entries in one of the correct formats, or leave the box blank, an invalid entry does not generate the validation text, instead it generates this message:

There is a 'Type mismatch' in the form control's Validationrule property.

What am I doing wrong?

 

[Rabbie]

I have used the following as the validation rule:

="No Progress" Or Between 0 And 100 Or Is Null


There is a 'Type mismatch' in the form control's Validationrule property.

What am I doing wrong?

There is a type mismatch between "No Progress" which is Text/String and Between 0 And 100 which is expecting an integer. You should validate this field using VBA in the BeforeUpdate event.

 

[82412]

There is a type mismatch between "No Progress" which is Text/String and Between 0 And 100 which is expecting an integer. You should validate this field using VBA in the BeforeUpdate event.

Thanks for the swift reply. Sorry, but I don't know how to do that. Can you expand? (I know how to get in to the VBA, but I don't know what to insert)

 

[Rabbie]

Because you are allowing two different types of iput into this box you cant use the standard validation rules so you will have to validate it using VBA code like this.

 if not(me!controlname = "No Progress" or (me!controlname > -1 and me!controlname < 101) or isnull(me!controlname) )then
     msgbox "Invalid Data Message"
      cancel = true
end if

You need to put this in the beforeupdate event for the field. You do this by right clicking in the field when it is open in design mode. Then click on properties and open the Events tab. Click on the field for the BeforeUpdate event and select Event Procedure. Then click the ... on the right. This will open the VBA editor in the procedure. Put the code in and test.

end if

 

[82412]

Problem solved, many thanks. (If you ever want to know anything about motorbikes, I'm your man)