Solved

Query wildcards no longer working

Posted on 2013-06-07
5
381 Views
Last Modified: 2013-06-25
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.
0
Comment
Question by:jyost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39229325
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39229411
If that doesn't help out, post your query's SQL.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39229552
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
 

Author Comment

by:jyost
ID: 39229633
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39275036
"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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question