CODEWITHSUNDEEP
regexms-accessvba
ahsoe
ahsoe

Reputation: 103

Access VBA Regex: Input mask validation. Check if string is comprised of repeating pattern only

I am trying to restrict a user from inputting a wrong string in a form field in Access.

I am trying to determine whether a string is of the form "1, 2, 54, 765, 43222". The sequence is

  1. a strictly positive integer
  2. a comma
  3. a space (␣)

and this sequence can be repeated an unknown number of times.

I have tried using the String Like function with "#, #" but have hit my head against the wall on how to allow this pattern to possibly repeat.

I am also open to Regex patterns, even though, I have a very limited amount of knowledge and understanding of this. I have tried with a pattern "\d+, +" and "(\d+, )+", but I am not convinced this is correct.

Another solution could be using substring and searching for single occurrences of the pattern in the first three characters of the supplied string, trimming the string removing the first three characters and repeating this action.

To give you some examples:

I hope my description is sufficient.

Thanks in advance.

Upvotes: 2

Views: 890

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

I have simple solution:

1) first, split the whole string:

numberArray = Split(YourSequence, ", ")

now, numberArray should contain only integers, if the string was correct,

2) secondly, you iterate through the numberArray in a loop and check if particular element can be converted to a number using IsNumeric function, if any element cannot be converted, then your answer is false and you can exit loop.

The code is easy and doesn't require any regular expressions.

Upvotes: 3

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626861

The \d+, + and (\d+, )+ patterns are not appropriate since they will find partial matches ("22, " in abc 22, end) and the second one - even if you fix the previous issue by adding anchors (^ and $), will still require , + a single space at the end of the string.

You need to use

^\d+(?:, \d+)*$

See the regex demo.

If at least 1 comma is required, replace the * quantifier with + to match 1 or more occurrences of the grouped subpattern.

Details:

  • ^ - start of the string
  • \d+ - 1 or more digits
  • (?:, \d+)*- 0+ sequences of
    • , - comma
    • - space
    • \d+ - 1 or more digits
  • $ - end of string

Upvotes: 1

Related Questions