Access 2007 Use regular expressions to find ###.#

Hello NGTLD,

If you are looking for records where the entire contents of the column is ###.#, then Markus's query will do it.

If you need something like "column contains ###.#", then you could go with:

SELECT afield FROM atable WHERE afield Like '*[0-9][0-9][0-9].[0-9]*';

Be advised, though, that that could also match the following:

1111.2
111.22
1111.22
etc

If you need something more complicated than that, then Regular Expressions may be the way to go.  You can
see this article for more info on using RegExp in Office:

http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

If you need help implementing any of the functions documented there, please let me know.

Regards,

Patrick

I am actually trying to create a function that strips everything from the field except the values I want.

NGTLD said:
>>I am actually trying to create a function that strips everything from the field except the values I want.

OK, then I have some follow-up questions...

1) Can there ever be more than one sequeunce ###.# in the column value?  If so, what should the result be?

2) What about a sequence like 12345.6789?  Would you want to grab just the portion that matches (ie, 345.6)?
Would you want the entire sequence (ie, 12345.6789)?  Would you reject it out of hand?

Hey Patrick,

There is a much nicer way to refer to your article (nice work by the way!), which works even in plain text comments:

Please refer to my article "Using Regular Expressions in VB" {http:\A_1336.html}.

Come to think of it, I should ask if this shortcut is a stable feature...
(°v°)

There would never be more than one instance per field record.
I want the entore sequence which would be one of the following

123.4
12.3

Markus,

Thanks for the reminder about the short links for articles.  I'll have to try and remember to use them :)

Glad you enjoyed the article.  I use those RegExp functions frequently :)

Patrick

NGTLD,

BTW, I used the pattern:

      \d+(\.\d+)?

That matches:
1) 1 or more digits
2) Followed by a period and then one or more digits, with this part occurring zero or one time.

So, it matches...

1
123
12345
1.2345
12.345
123.45
1234.5

etc.

If your need is more specific, I can refine the pattern.

Patrick

Thanks that got me most of the way there. I read your article and tried to get the rest of the way there. I also need to match the call letters which are four letters and always begin with W. Can you point me in the right direction?

NGTLD,

Um, what call letters?

:)

In all seriousness, I think your original question has been answered...

Patrick

OK thanks for helping