Problem with a parameter query

[Terry]

I have a parameter query I have built to run from a form. It has 4 fields
for which parameters can be chosen form a combo box control. Three of the
fields work fine. The 4th is giving me the problem. The 3 that work all
have one word choices. In the 4th, some choices are multiple words separated
by spaces. If I choose a single word choice from the list it works fine. If
I choose a multiple word choice, it returns a null record. My Criteria
expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

The thing that really has me puzzled is I can create the say query as a
simple parameter query, enter multiple words in the parameter dialog box when
it opens, and it works fine. Does anyone see something I am missing that
would cause this to not accept multiple words? Thanks.

 

[Bob Barrows]

I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search for
records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or columnname
like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query as
a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was generated.
Show us a few rows of sample data and show us what the query should
return.

 

[Terry]

The query should return the contact information for any volunteer who has
given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In the
Volunteer Area field, that is listed as Home Adult. That is also a choice in
the Volunteer Area combo box. If Home Adult is chosen from the combo box and
the query is run, it returns a null record. I have checked the database and
I know there are volunteers matching that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and run the
query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting a
choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search for
records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or columnname
like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query as
a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was generated.
Show us a few rows of sample data and show us what the query should
return.

 

[Douglas J. Steele]

When you say that there are volunteers matching the Home Adult criteria,
exactly what is stored for them? Unless the exact string "Home Adult" is
contained at some point in what's stored, the query appears to be working as
it should.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The query should return the contact information for any volunteer who has
given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In the
Volunteer Area field, that is listed as Home Adult. That is also a choice
in
the Volunteer Area combo box. If Home Adult is chosen from the combo box
and
the query is run, it returns a null record. I have checked the database
and
I know there are volunteers matching that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and run
the
query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting a
choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search for
records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or columnname
like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query as
a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was generated.
Show us a few rows of sample data and show us what the query should
return.

 

[Terry]

Never mind. I found the issue. The person who built the table the combo box
was pulling from put 2 spaces between words. I took out the extra space and
everthing works fine. Oh the joys of fixing things you didn't build!!
--
Terry L. Adcock
Microsoft Master Instructor

The query should return the contact information for any volunteer who has
given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In the
Volunteer Area field, that is listed as Home Adult. That is also a choice in
the Volunteer Area combo box. If Home Adult is chosen from the combo box and
the query is run, it returns a null record. I have checked the database and
I know there are volunteers matching that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and run the
query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting a
choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search for
records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or columnname
like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query as
a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was generated.
Show us a few rows of sample data and show us what the query should
return.

 

[Bob Barrows]

How is the combo box populated?

The query should return the contact information for any volunteer who
has given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In
the Volunteer Area field, that is listed as Home Adult. That is also
a choice in the Volunteer Area combo box. If Home Adult is chosen
from the combo box and the query is run, it returns a null record. I
have checked the database and I know there are volunteers matching
that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and
run the query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting
a choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search
for records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or
columnname like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query
as a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was
generated. Show us a few rows of sample data and show us what the
query should return.

 

[Terry]

Thanks. I found the error. The person who built the table the combo box was
pulling from had put extra spaces between the words. I removed the extra
spaces and it is working fine.
--
Terry L. Adcock
Microsoft Master Instructor

When you say that there are volunteers matching the Home Adult criteria,
exactly what is stored for them? Unless the exact string "Home Adult" is
contained at some point in what's stored, the query appears to be working as
it should.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The query should return the contact information for any volunteer who has
given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In the
Volunteer Area field, that is listed as Home Adult. That is also a choice
in
the Volunteer Area combo box. If Home Adult is chosen from the combo box
and
the query is run, it returns a null record. I have checked the database
and
I know there are volunteers matching that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and run
the
query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting a
choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

Terry wrote:
I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search for
records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or columnname
like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query as
a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was generated.
Show us a few rows of sample data and show us what the query should
return.

 

[Terry]

Thanks. I found the problem. The person who created the table populating
the combo box had added extra spaces between words. When I removed the extra
spaces everything worked fine.


--
Terry L. Adcock
Microsoft Office Master Instructor

How is the combo box populated?

The query should return the contact information for any volunteer who
has given the chosen area as an area they would volunteer in.

For example, one of the areas is home visits for adult patients. In
the Volunteer Area field, that is listed as Home Adult. That is also
a choice in the Volunteer Area combo box. If Home Adult is chosen
from the combo box and the query is run, it returns a null record. I
have checked the database and I know there are volunteers matching
that criteria.

If I build a simple parameter query and type in Home Adult, I get the
expected results.

If I choose a one word choice, like Cooking, from the combo box and
run the query, I again get the expected results.

So there seems to be a problem with the way the query is interpreting
a choice from the combo box that includes spaces.

--
Terry L. Adcock
Microsoft Master Instructor

Terry wrote:
I have a parameter query I have built to run from a form. It has 4
fields for which parameters can be chosen form a combo box control.
Three of the fields work fine. The 4th is giving me the problem.
The 3 that work all have one word choices. In the 4th, some choices
are multiple words separated by spaces. If I choose a single word
choice from the list it works fine. If I choose a multiple word
choice, it returns a null record. My Criteria expression is:

Like "*"&[Forms]![CallCenterFormNew]![VolunteerArea]&"*"

If I am understanding this correctly, this is the defined behavior. A
parameter contains a single value. If a user chooses a selection
containing "this is four words", then your query is going to search
for records where the relevant column contains "this is four words"
somewhere in it. It will not try to parse the value and do this:
where columnname like '*this*' or columnname like '*is*' or
columnname like '*four*' or columnname like '*words*'

The thing that really has me puzzled is I can create the say query
as a simple parameter query, enter multiple words in the parameter
dialog box when it opens, and it works fine. Does anyone see
something I am missing that would cause this to not accept multiple
words? Thanks.

Switch it to SQL View and look at the sql statement that was
generated. Show us a few rows of sample data and show us what the
query should return.