Re: wildcards in parameter queries don't work -access 2003

Tom,

Let me start over step by step.

Step 1:
I have a query. The query works perfect. I enter the criteria with the
wildcards "*John Smith*" and it renders all the records. No problem.

Step 2:
I wanted to make a parameter query. Instead of entering the actual values
figures, you prompt the query user to enter the criteria values. So I set
up a parameter query by entering the Criteria text in the query grid and
surrounded it with brackets[]. No problem.

Step 3:
I ran the parameter query. A dialog box appears with the message I created
"Enter Company Name". No problem.

Step 4:
I type the value, *John Smith*, to retrieve all the records. I get nothing.
If I type John Smith with no wildcards, I get one record. Any ideas?

Again, Thanks for your help.

Tracey


"Tom Ellison" wrote:

Dear Tracey:

Your original question had to do with using wildcards and finding 4
different companies that contain "John Smith" in their names. It seems
we're after something somewhat different.

Your query now prompts you to "Enter Company Name" and the response is
exactly: "John Smith" but it doesn't give the same results. So, how are
the results different? More rows? Fewer rows? Different rows? No rows?
Please give as much information as you have. If you are entering exactly
"John Smith" and not getting the same results then something is definitely
funny. But you have to be spelling John Smith exactly the same way.

Tom Ellison


"tracey@flexus" <traceyflexus@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B919611-1B52-4C91-B092-A366EF5EA706@xxxxxxxxxxxxxxxx
Tom,

Here is the SQL for the regular query criteria:
SELECT DISTINCTROW Customer.Company, Customer.MaintExpireDate,
Customer.Maintdollar, Customer.[Authorized User First],
Customer.[Authorized
User Last], Customer.[Licensee First], Customer.[Licensee Last],
Customer.Addr1, Customer.Addr2, Customer.City, Customer.State,
Customer.Zip,
Customer.Country, Customer.email, Customer.sp2serialnum,
Customer.sp2version,
Customer.compilr, Customer.opsys, Customer.[num users], Customer.phone,
Customer.fax, Customer.notes, Customer.product
FROM Customer
WHERE (((Customer.Company)="John Smith"))
ORDER BY Customer.Company;

This works fine. All records show. However, when I do the Parameter
Query,
I doesn't give the same results.

Here is the SQL for the Parameter
SELECT DISTINCTROW Customer.Company, Customer.MaintExpireDate,
Customer.Maintdollar, Customer.[Authorized User First],
Customer.[Authorized
User Last], Customer.[Licensee First], Customer.[Licensee Last],
Customer.Addr1, Customer.Addr2, Customer.City, Customer.State,
Customer.Zip,
Customer.Country, Customer.email, Customer.sp2serialnum,
Customer.sp2version,
Customer.compilr, Customer.opsys, Customer.[num users], Customer.phone,
Customer.fax, Customer.notes, Customer.product
FROM Customer
WHERE (((Customer.Company)=[Enter Company Name]))
ORDER BY Customer.Company;

I thought it was simple but I'm doing something wrong. Any suggestions?

Thanks for your help!

Tracey

"Tom Ellison" wrote:

Dear Tracey:

Probably you are viewing this query in "design view". There is a control
at
the upper left of the screen (unless you have moved it) that looks a bit
like a screen rectangle with a downward arrow to the right. By default,
this is directly under the word "File". Click the down arrow and you
should
see a list of alternative view. Choose the SQL View. Copy all the text
there into your post.

Tom Ellison


"tracey@flexus" <traceyflexus@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ED898D7B-CC2C-4CD6-86A2-56D929DFE491@xxxxxxxxxxxxxxxx
The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

"Tom Ellison" wrote:

Dear Tracey:

I cannot see your code, but I'm guessing it was originally:

WHERE SomeColumn LIKE "*John Smith*"

and that you then changed it to:

WHERE SomeColumn LIKE "*" & [Enter Name: ] & "*"

In the above case, entering "John Smith" should give the same results
as
the
former version. So, have you varied from this in some way?

Tom Ellison


"tracey@flexus" <tracey@flexus@discussions.microsoft.com> wrote in
message
news:9E316268-9F76-4F6C-B53D-949838035943@xxxxxxxxxxxxxxxx
Why do the wildcards work in the design criteria of a query but
don't
work
in
a parameter query?

For example:
If I have the following 4 companies listed in my database:
The John Smith Company
John Smith Inc
New World Co (John Smith)
John Smith

When entering *John Smith* in the queries criteria, all 4 records
show.
However, when entering the same thing in the parameter query, only
one
record
is found and that is John Smith. I tried various wildcards but the
results
never found all 4 records. How do I get the parameter query to find
all 4
records?









.

Relevant Pages

  • Re: Getting Criteria for Query from VB
    ... It is not clear from your post what the query criteria are supposed to ... > using VB to pass criteria to a parameter query based on a command button. ... > The report opens fine, and such, but right now I've got a seperate report ...
    (microsoft.public.access.modulesdaovba)
  • Re: wildcards in parameter queries dont work -access 2003
    ... "Here is the SQL for the Parameter ... The query works perfect. ... I type the value, *John Smith*, to retrieve all the records. ... "Tom Ellison" wrote: ...
    (microsoft.public.access.queries)
  • Re: wildcards in parameter queries dont work -access 2003
    ... "Tom Ellison" wrote: ... I have a query. ... I wanted to make a parameter query. ... I type the value, *John Smith*, to retrieve all the records. ...
    (microsoft.public.access.queries)
  • Use adp form control as criteria for sql function
    ... I use Forms!FormName!ControlName in the Query criteria ... he enters a % sign as a wildcard filter. ... Any idea's on how to create a drop down list in the parameter query ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Parameter Query Issue
    ... 2- In the criteria of your query have the following: ... Criteria: TestIfOpen ... Microsoft Access Support ... | Thread-Topic: Parameter Query Issue ...
    (microsoft.public.access.queries)