0

I have an old-fashioned Access database (.mdb). I have a macro-enabled Excel spreadsheet that interacts with this database via an ADODB connection.

I am wanting to perform a query on the database using a regular expression in the "like" clause; something like:

(I want to match "SM39_002xx")

"SELECT Serial from tbl1939 where Serial like RegExpMatch(""^SM+[0-9]+[_][0-9]+[a-z]?"", [Serial])"

which works perfectly within Access, but from Excel the "RegExpMatch" function isn't found and the whole thing fails.

Any help much appreciated.

Alex McMillan
  • 17,096
  • 12
  • 55
  • 88
  • I can't find `RegExpMatch` in MSDN (wow), but are you sure the regular expression should be quoted in double quote ("), not single quote (')? – Passerby Feb 01 '13 at 03:24
  • you want to match `SM39_002xx` but how does the input data look like – Anirudha Feb 01 '13 at 03:28
  • What happens if you create that as a query and select from the query in Access? does it work? Now what happens if you select from that querydef in Excel? does it work? What exact error do you get? – Nick.Mc Feb 01 '13 at 04:44
  • 1
    I believe `RegExpMatch` is a UDF in Access and hence Excel cannot find it. See an example from here http://stackoverflow.com/questions/5539141/microsoft-office-access-like-vs-regex – Siddharth Rout Feb 01 '13 at 05:58

1 Answers1

0

User Defined Functions (UDF), which I am fairly certain is what you have in RegExpMatch, are only available within MS Access. For the most part, you should avoid creating them unless there is no other option. For example, the following query will run outside MS Access and approximates what you want:

SELECT t.Field1
FROM ATable t
WHERE t.Field1 Like "SM[0-9][0-9][_][0-9][0-9][0-9][a-z]?"
Fionnuala
  • 90,370
  • 7
  • 114
  • 152