Query wildcards no longer working

I'm having issues using the # wildcard character in an Access query.  Previously, I've had no issues whatsoever, but now I cannot get any results returned with one.  

The search criteria

Like "*test*local"    returns

233test.local
469test.local
578test.local
233test.local

but Like "###test*local" returns nothing.  

This query used to work, but after moving the tables to a new database, it no longer functions.  Am I missing something?

Thanks.
jyostAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor Commented:
Did you move the data to SQL Server (or a SQL compatible Access database), or are you using an ADO recordset?

If so, you may have to change your wildcards to:


Like "[0-9][0-9][0-9]test%local"
0
 
mbizupCommented:
If that doesn't help out, post your query's SQL.
0
 
datAdrenalineCommented:
What is the option you have set for the option named "SQL Server compatible syntax (ANSI 92)"?

In A2010:
File | Options | Object Designers | Query Design | SQL Server compatible syntax (ANSI 92)

By the way ... you are speaking of an Access Query object right?
0
 
jyostAuthor Commented:
Mbizup:  The [0-9][0-9][0-9] syntax worked.  Thanks.  I didn't change anything in the databases to make them SQL compatible though.

datAdrenaline: I have nothing checked in those options. "This database" is grayed out and "default" is unckecked.  What would this change?  If those were checked, could I use different syntax?

Thanks to both.
0
 
datAdrenalineCommented:
"What would this change?  If those were checked, could I use different syntax?"

The only way that changes is if someone does it.  Many times people experiment with settings and don't see an immediate visual difference.

If you change that option, you Query objects would have to be written in ANSI 92 compliant syntax.

For more info, check out the following:
http://office.microsoft.com/en-us/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx
0
All Courses

From novice to tech pro — start learning today.