jyost
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If that doesn't help out, post your query's SQL.
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?
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?
ASKER
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.
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.
"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
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