Regexp Access VBA - multiplier not working

0
votes

I am trying to filter a table from Access using VBA DAO.Recordset. I want to match patterns like: \w{3}\d{5} ex: ABC12345

This works fine if I test only in VBA code (in excel) but when I execute the recordset no matches are found. The shorthand character classes \w,\d and multiplier part {3} are ignored

Simple queries like: select * from table where column like '*C123*'" or "'*[C123]*' , work just fine.

I have found a solution but it is very ugly: [A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]

1
regexexcelvbams-access
Does [A-Z]{3}[0-9]{5} work? – Diego
No, I tried and it does not work – Cornel
LIKE does not support regex, only wildcards. – Wiktor Stribiżew
Maybe this answer can help you: stackoverflow.com/a/5539504/310276 – Diego

1 Answers

1
votes

like 'xy' is not a (posix)regex, it's a sql-statement. see Access SQL: https://msdn.microsoft.com/en-us/library/bb208897(v=office.12).aspx or maybe T-SQL https://msdn.microsoft.com/de-de/library/ms179859(v=sql.120).aspx But the exact possibilities may depend on the connected database, and which engine is executing the statemenr.

Following query might work.

select * from table 
where customer like '[a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9][0-9]'

Regular expressions work quite different in each implementation.