wildcards in parameter queries don't work -access 2003

[tracey@flexus]

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?

 

[Tom Ellison]

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]

The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

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

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?

 

[Tom Ellison]

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

The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

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


message

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?

 

[tracey@flexus]

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

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

The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

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


message
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?

 

[Tom Ellison]

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

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

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

The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

:

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


message
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?

 

[tracey@flexus]

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

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

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

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


The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

:

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


message
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?

 

[Tom Ellison]

Dear Tracey:

Earlier, you posted:

"Here is the SQL for the Parameter

WHERE (((Customer.Company)=[Enter Company Name]))"

Now, the wildcards, such as "*" don't work with equals (=) but only with
LIKE. If you use equals, then it will find strings that have exactly what
you type, that is, a literal asterisk, not a wildcard.

Tom Ellison

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

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

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

:

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


message
The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

:

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


message
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?

 

[tracey@flexus]

Awesome....it works!

Wow, I knew it was a simple problem; I just didn't realize how simple. I
thought the problem was with the way in which I was typing the value in the
parameter dialog box. I didn't know I had to enter "Like" in the criteria of
the query grid followed by the criteria text surrounded by brackets.

Thanks so much for all your help. I truly appreciated it!

Dear Tracey:

Earlier, you posted:

"Here is the SQL for the Parameter

WHERE (((Customer.Company)=[Enter Company Name]))"

Now, the wildcards, such as "*" don't work with equals (=) but only with
LIKE. If you use equals, then it will find strings that have exactly what
you type, that is, a literal asterisk, not a wildcard.

Tom Ellison

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

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


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

:

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


message
The SQL for the query criteria looks like this:
WHERE (((customer, company) Like "*John Smith*"))

How do I check the SQL of the parameter?

:

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


message
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?