3

From wildcard characters supported by Microsoft Access I cannot get the # working in SQL statement. A minimalized example:

SELECT COUNT(*) FROM MSysObjects WHERE "123" LIKE "#*"

return value for "#*": 0
return value for "*": 56

It works only in the VBA Immediate Window (I know that the evaluation there is actually different, but anyway):

? "123" LIKE "#*"
True

All other pattern characters seem to work fine. But since the above linked page declares the # to be usable in SQL statements, is this a bug? Or am I misreading the documentation?

Version info says: Version 1905 (Build 11629.20246 Click-to-Run)

Setting SQL Server Compatible Syntax (ANSI 92) is off. (In Options > Object Designers.)

UPDATE: A commenter says it works as expected in Access 2010. It also works in Access 2007 and 2016 Pro Plus and in Access Office 365 Business 1906 11727.20230.

I tried changing collation to General (Options > General > New Database Sort Order) and then creating a new database but the issue is still present there.

miroxlav
  • 11,796
  • 5
  • 58
  • 99
  • There are several syntactical differences between the Jet engine and the Access user interface (including VB). If using the Jet engine directly, use SQL wildcard characters. – rd_nielsen Jul 09 '19 at 21:46
  • @rd_nielsen – yes but could you show me what I am not doing correctly compared to the official documentation? – miroxlav Jul 09 '19 at 21:47
  • More information is here: https://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign – rd_nielsen Jul 09 '19 at 21:54
  • @rd_nielsen – sorry, that is a different topic, not addressing my question – miroxlav Jul 09 '19 at 21:57
  • 1
    The code works as expected in Access 2010. – forpas Jul 09 '19 at 22:04
  • @forpas – yes, this is what I was afraid of... it worked and it stopped since some version. Thanks for trying it out. – miroxlav Jul 09 '19 at 22:05
  • Can confirm non-zero results in Access 2007 & 2016 Pro Plus (1906) – Lee Mac Jul 09 '19 at 22:12
  • Can confirm non-zero results in Access Office 365 Business 1906 11727.20230 – Lee Mac Jul 09 '19 at 22:28
  • @LeeMac – Thanks for trying all that. Then it may look like an issue caused by an application environment? I changed Options > General > New Database Sort Order := General and created a new database, but the issue still persists there. Will wait for arrival of Office 1906 which you used for testing. – miroxlav Jul 09 '19 at 22:56
  • For the sort order, check `CurrentDb.CollatingOrder`, it tends to behave finicky (needs a compact & repair before it takes effect, changing it only changes it for a specific database for the next compact and repair). – Erik A Jul 10 '19 at 07:05
  • Also note: Access supports per-field collation. If you're comparing 2 constants, I wouldn't have a clue what collation is used. For tests, it's more sensible to compare to an actual field and check that fields collation first (`CurrentDb.TableDefs("SomeTable").Fields("SomeField").CollationOrder`). The database collation order will change the default for new fields, but I'm not sure if the database collation order is used when comparing two constants. – Erik A Jul 10 '19 at 07:11
  • @ErikA – Thanks, I'll check it. Since other characters always work, this now looks like a bug anyway. Will wait for Access ver. 2019-06 to confirm the behavior there. – miroxlav Jul 10 '19 at 09:35

1 Answers1

0

In Access, the numbers symbol (#) is the delimiter for dates stored as a string.

You need to "escape" the special character, by wrapping it in square brackets, like this:

SELECT Count(*) AS Expr1 
FROM MSysObjects 
WHERE (((MSysObjects.[Name]) Like "[#]*"));

Note: your query is searching for a string starting with a hash inside a literal string "123" - that probably won't work either.

trevor
  • 257
  • 3
  • 9