Like Filter in 07

[ainow]

Like Filter in 07: I imported all the local tables, forms, queries, reports
and modules and linked the external tables into a blank 07 database from a
database that was originally written in version 2003 and then converted to
2007. When I applied a filter to a memo a field using “Like†the new
database returned no records – works in the original database. Both filter
statements are the same.

I built a test query in both databases for the same linked table and the
same memo field with the following Criteria: Like “*Amy*â€. The original
database worked (returned 239 of 7216 records) and the new did not (returned
no records) and it changed the query criteria statement from Like “*Amy*†to
Alike “*Amy*â€.

I built the new database because the original used a tool bar which is not
support in 07.

What did I miss?

Jim

 

[John Spencer]

Try changing the criteria to use ANSI wildcards.

ALIKE "%AMY%"

If that works then check the settings of your database to see if it is
set to use ANSI compatible SQL.

Beyond that I can't suggest anything. I have no way to check as I am at
home and do not have Access 2007 installed.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ainow]

ALike "%amy%" did not work.

SQL compatible is set to ANSI92.

I find it interesting that both databases are 2007 but do not work the same.
I now have two projects that are not deliverable because they have been
“updated†to 2007.

Jim

 

[John Spencer]

I should have told you to try if you are using ANSI 92 compatibility.

LIKE '%Amy%'

OR uncheck ANSI 92 compatibility and try LIKE "*Amy*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ainow]

With ANSI 92 unchecked Like "%amy%" did not work but Like "*amy*" DID work.

Thanks
Jim