Finding records containing numeric digits by using '[0-9]' or '#'

[Kevin T]

Hi again,

I would like to find some numeric sequences in a .accdb file. Something in the form of '%[0-9]%' or '%#%' seems to be appropriate based on this article:

http://www.techrepublic.com/article/10-tips-for-using-wildcard-characters-in-microsoft-access-criteria-expressions/

However, these queries does not return any results:

SELECT * FROM TestCase1 WHERE Discrepancy LIKE '%[0-9][0-9][0-9][0-9]%'
SELECT * FROM TestCase1 WHERE Discrepancy LIKE '%####%'

Any help would be appreciated.

Thanks,
Kevin

[Gord Thompson]

I just ran some tests with UCanAccess 3.0.4 and both queries worked if I used an asterisk (*) instead of a percent sign (%) as the wildcard character, i.e.,

sql = 
        "SELECT * FROM TestCase1 " +
        "WHERE Discrepancy LIKE '*[0-9][0-9][0-9][0-9]*'";

sql = 
        "SELECT * FROM TestCase1 " +
        "WHERE Discrepancy LIKE '*####*'";

 
Wildcard support for LIKE with Access itself is a bit confusing, depending on

• where the query is run (in Access itself, or from an external application),
• whether the database is configured for "SQL Server Compatible Syntax", and
• whether the query uses the LIKE or the ALIKE keyword.

It appears that UCanAccess has opted for * since that is probably what the majority of Access users would expect.

[Kevin T]

Works great with the asterisk instead of the percent sign. Thanks, Gord!