Query wildcard symbols

[Jeff Boyce]

Just a heads-up in case this bites you, too.

I migrated an application from Access 2002 to Access 2007.

I have a search form for the users to enter some criteria ... the form
dynamically builds a SQL WHERE clause before opening a "results" form based
on an underlying query that includes all the fields available to search on.

Once in Access 2007 format, the search form opens empty every time?!

When I add selection criteria to the underlying query directly, the result
set is empty every time?!

Oh wait, I'm using the "*" (anything/everything) wildcard character. Why
isn't it working?

Nothing in HELP, nothing at Microsoft, nothing via Google, what's going on?!

Bottom line:

Access 2007 comes with the ANSI 92 standard enforced by default. The ANSI
92 standard uses the "%" wildcard character, not the "*".

How hard would it be for Access to notice that the query included a "*" in
the selection criteria and the Like (Alike) operator, but had the ANSI 92
standard in force? And offer a little suggestion ... like "try using "%"
instead of "*""?!

There, I feel a lot better now ... kinda "Primal Screen" therapy...

Jeff Boyce
Microsoft Office/Access MVP

 

[NetworkTrade]

thank you for this. I've taken to scanning this site almost daily always
looking for 2007 in the question headline to help me learn the differences
with this newer version....plus have the link to AllenBrowne's info as a
favorite....

 

[Jerry Whittle]

Well in a way that is good news for me as I use Oracle a lot which is ANSI
92. Now if Access would just go to single quotes instead of double quotes in
SQL statements...

Thanks Jeff!

 

[Jeff Boyce]

Small (but significant) correction ... the "results" form opens empty, not
the search form.

Jeff

 

[John W. Vinson]

Now if Access would just go to single quotes instead of double quotes in
SQL statements...

It'll take either in most cases.