Validation rule that allows any number of digits and spaces?

[sirkistova]

I need to make a validation rule that would allow digits and spaces - any number.
Something like:
Like "[0-9 ]*"
but I need a '*' whose meaning is "repeat zero to infinit times" like in regexp.
How do I do that? What is the repeat charachter in Access validation rule?
The field is a list of member ids seperated by spaces. It would be even better if the validation rule would allow the integer numbers in the list be not bigger than the higher member id. (But I guess this is too good to be possoble.)
Thanks
Tova

 

[Galaxiom]

Random thoughts. You can cobble something together from this.

IsNumeric(Replace([fieldname]," ", ""))

(It might even work without the Replace. Try it.)

However this has a weakness. An E will be treated as the Exponent separator returning True from the IsNumeric. So you should test that there is no "E" with the InStr() function.

Use a DMax to get the biggest value from the table and compare it.

DMax("fieldname", "tablename")

You might find IsError() function and the IIF() function useful as part of the expression.

Another way is to use a function where you loop though the entry one character at a time and ensure the ascii value of each charaacter is between Asc("0") and Asc("9") or Asc(" "). (These values are 48, 57 and 32 respectively.)

 

[sirkistova]

Where should I place this code? In lost-focus subroutine?
What if validation failed - I need in that case to pop a msgbox and hold the focus in the field and hold the gui on the current record (in case focus was lost to the arrows thingy that navigates over records) - how can I hold these two?
Thanks
Tova

 

[Beetle]

I think it might be a good idea for you to clarify what it is you are trying to do here.

The field is a list of member ids seperated by spaces.

So you have one field in which you want users to enter the MemberID's of different members, separated by spaces?

If so, then the most important question here is why are you trying to do this?

It would be even better if the validation rule would allow the integer numbers in the list be not bigger than the higher member id.

The way this is written you seem to be saying that you want a validation rule that forces the users to enter a number that is less than or equal to the highest MemberID in the database? Again, on the surface this doesn't seem to make a lot of sense.

 

[sirkistova]

Thanks, I managed to do it.
I placed the suggested code in the field's lost-focus sub.
Using the form's before-update sub, I can hold the current record if validation failed. (I searched the forum and found what I needed in this case.)
And the 'E' problem is solved by:
If (InStr(ReceiverList, "E") Or Not IsNumeric(Replace(ReceiverList, " ", ""))) Then
MsgBox "You should only enter numbers"
Thanks!!
Tova

 

[qrodrigob]

This is my simple validator model.
Obviously you will be improved, but it is used to return the specific digit, for simple systems.

 

[jdraw]

qrodrigob,
I have a feeling that sirkistova will not try your validador model -- the post was 13 years ago.

 

[qrodrigob]

Well, it doesn't have to be him exactly, but as this is recorded on the internet, it's possible that someone else in the future will need a solution, and maybe they can help.

When something is published on the internet, it is not just for a single person, but for everyone with the same doubts and opportunities.