2

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:

  • "1, 2, 3, 4" should return true
  • "1,2, 3, 4" should return false
  • "a, 3, 65!, 34" should return false
  • "1, 3, 2, 4, 5442" should return true

I hope my description is sufficient.

Thanks in advance.

Erik A
  • 28,352
  • 10
  • 37
  • 55
ahsoe
  • 83
  • 12

2 Answers2

3

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.

Michał Turczyn
  • 28,428
  • 14
  • 36
  • 58
1

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
Wiktor Stribiżew
  • 484,719
  • 26
  • 302
  • 397
  • Let me know if you need more details. – Wiktor Stribiżew Mar 08 '17 at 10:58
  • Hi Wiktor. Thanks for your quick and detailed reply. This is exactly what I need. "12" should for instance return true, so 1 comma is not needed. One thing though: what does ?: mean exactly in the (?:, \d+)* part? – ahsoe Mar 08 '17 at 11:20
  • That is a [*non-capturing group*](http://stackoverflow.com/questions/3512471/what-is-a-non-capturing-group-what-does-a-question-mark-followed-by-a-colon) that is only used for grouping subpatterns and does not store the captured value in the memory buffer. Since the group is quantified, and we are not interested in what text is captured, there is no point in using a capturing group here. – Wiktor Stribiżew Mar 08 '17 at 11:22