Query criteria will not pick up a combo's column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Thanks.
David
 
Don't be offended, but you know that Column starts counting at zero and that
Column(1) refers to the 2nd column of the combobox, right?
 
yes.

George Nicholson said:
Don't be offended, but you know that Column starts counting at zero and that
Column(1) refers to the 2nd column of the combobox, right?

--
George Nicholson

Remove 'Junk' from return address.


David said:
Hi,
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function
procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Thanks.
David
Click to expand...
Click to expand...
 
David said:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Click to expand...


I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
 
Hi Marsh,
Thanks for the excellent answer.
At least now I know textbox should work.
( I had my query point to the text box using your suggested syntax, and
it works.)

That bring me to ask the following (new) question:

NEW QUESTION:
(1) I need to pick up all the highlighted choces from a multi-select list
(or a "simple" list), using one textbox to hold each of the selected choice
of list items.
Question is how may I sepecify the control source for each of the textboxes.
The following will always pick up the last list items that has been clicked
on:
=[LISTNAME].Column(0)
How may I modify it to do the job?

David


Marshall Barton said:
David said:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Click to expand...


I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
Click to expand...
 
david said:
Hi Marsh,
Thanks for the excellent answer.
At least now I know textbox should work.
( I had my query point to the text box using your suggested syntax, and
it works.)

That bring me to ask the following (new) question:

NEW QUESTION:
(1) I need to pick up all the highlighted choces from a multi-select list
(or a "simple" list), using one textbox to hold each of the selected choice
of list items.
Question is how may I sepecify the control source for each of the textboxes.
The following will always pick up the last list items that has been clicked
on:
=[LISTNAME].Column(0)
How may I modify it to do the job?
Click to expand...

This a weird thing to do. How are the text boxes bound to a
data table?? Certainly not to fields named something like
Name1, Name2, etc, are they?

I never use a list box so my answer may not be a good way,
but you can get to the selected items through the
ItemsSelected property:

textbox1 = ListName.Column(0, ItemsSelected(1))
textbox2 = ListName.Column(0, ItemsSelected(2))

See Help for details.

BTW, it is better to start a new thread with a unrelated
question then to expect the same person to be able to do a
proper job on a new question.
--
Marsh
MVP [MS Access]

David said:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Click to expand...
Click to expand...
Marshall Barton said:
I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
Click to expand...
Click to expand...
 
I tried your formula. It didn't work. I tried it many times.
I did find the help (Access local/online) on ItemsSelected (and other
related keywords),
read everything available, and agreed that the way you suggested seems to be
consistent with what Access help says should be done.
BUT - it didn't work and is very frustrating.
Here is my actual code (I used design grid to "build" part of it, and
competed it by "hand"):
Control source for textbox #1:
=SITECRRT.Column(0,ItemsSelected(0))
Control source for textbox #2:
=SITECRRT.Column(0,ItemsSelected(1))
When the form is "run", I received this error message, in the text box:
#Name?

Any idea? I plan to post this as a new question so more people can get a
crack at it.


David

Marshall Barton said:
This a weird thing to do. How are the text boxes bound to a
data table?? Certainly not to fields named something like
Name1, Name2, etc, are they?

I never use a list box so my answer may not be a good way,
but you can get to the selected items through the
ItemsSelected property:

textbox1 = ListName.Column(0, ItemsSelected(1))
textbox2 = ListName.Column(0, ItemsSelected(2))

See Help for details.

BTW, it is better to start a new thread with a unrelated
question then to expect the same person to be able to do a
proper job on a new question.
Click to expand...
david said:
Hi Marsh,
Thanks for the excellent answer.
At least now I know textbox should work.
( I had my query point to the text box using your suggested syntax, and
it works.)

That bring me to ask the following (new) question:

NEW QUESTION:
(1) I need to pick up all the highlighted choces from a multi-select list
(or a "simple" list), using one textbox to hold each of the selected choice
of list items.
Question is how may I sepecify the control source for each of the textboxes.
The following will always pick up the last list items that has been clicked
on:
=[LISTNAME].Column(0)
How may I modify it to do the job?
David wrote:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)
Click to expand...
Marshall Barton said:
I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
Click to expand...
Click to expand...
Click to expand...
 
Sorry David, I left out the list box:

=SITECRRT.Column(0,SITECRRT.ItemsSelected(1))
--
Marsh
MVP [MS Access]


I tried your formula. It didn't work. I tried it many times.
I did find the help (Access local/online) on ItemsSelected (and other
related keywords),
read everything available, and agreed that the way you suggested seems to be
consistent with what Access help says should be done.
BUT - it didn't work and is very frustrating.
Here is my actual code (I used design grid to "build" part of it, and
competed it by "hand"):
Control source for textbox #1:
=SITECRRT.Column(0,ItemsSelected(0))
Control source for textbox #2:
=SITECRRT.Column(0,ItemsSelected(1))
When the form is "run", I received this error message, in the text box:
#Name?


Marshall Barton said:
This a weird thing to do. How are the text boxes bound to a
data table?? Certainly not to fields named something like
Name1, Name2, etc, are they?

I never use a list box so my answer may not be a good way,
but you can get to the selected items through the
ItemsSelected property:

textbox1 = ListName.Column(0, ItemsSelected(1))
textbox2 = ListName.Column(0, ItemsSelected(2))

See Help for details.

BTW, it is better to start a new thread with a unrelated
question then to expect the same person to be able to do a
proper job on a new question.
Click to expand...
david said:
Hi Marsh,
Thanks for the excellent answer.
At least now I know textbox should work.
( I had my query point to the text box using your suggested syntax, and
it works.)

That bring me to ask the following (new) question:

NEW QUESTION:
(1) I need to pick up all the highlighted choces from a multi-select list
(or a "simple" list), using one textbox to hold each of the selected choice
of list items.
Question is how may I sepecify the control source for each of the textboxes.
The following will always pick up the last list items that has been clicked
on:
=[LISTNAME].Column(0)
How may I modify it to do the job?

David wrote:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)


:
I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
Click to expand...
Click to expand...
Click to expand...
 
That works!
Thankz Marsh.
David

Marshall Barton said:
Sorry David, I left out the list box:

=SITECRRT.Column(0,SITECRRT.ItemsSelected(1))
--
Marsh
MVP [MS Access]


I tried your formula. It didn't work. I tried it many times.
I did find the help (Access local/online) on ItemsSelected (and other
related keywords),
read everything available, and agreed that the way you suggested seems to be
consistent with what Access help says should be done.
BUT - it didn't work and is very frustrating.
Here is my actual code (I used design grid to "build" part of it, and
competed it by "hand"):
Control source for textbox #1:
=SITECRRT.Column(0,ItemsSelected(0))
Control source for textbox #2:
=SITECRRT.Column(0,ItemsSelected(1))
When the form is "run", I received this error message, in the text box:
#Name?


Marshall Barton said:
This a weird thing to do. How are the text boxes bound to a
data table?? Certainly not to fields named something like
Name1, Name2, etc, are they?

I never use a list box so my answer may not be a good way,
but you can get to the selected items through the
ItemsSelected property:

textbox1 = ListName.Column(0, ItemsSelected(1))
textbox2 = ListName.Column(0, ItemsSelected(2))

See Help for details.

BTW, it is better to start a new thread with a unrelated
question then to expect the same person to be able to do a
proper job on a new question.
Click to expand...
david wrote:

Hi Marsh,
Thanks for the excellent answer.
At least now I know textbox should work.
( I had my query point to the text box using your suggested syntax, and
it works.)

That bring me to ask the following (new) question:

NEW QUESTION:
(1) I need to pick up all the highlighted choces from a multi-select list
(or a "simple" list), using one textbox to hold each of the selected choice
of list items.
Question is how may I sepecify the control source for each of the textboxes.
The following will always pick up the last list items that has been clicked
on:
=[LISTNAME].Column(0)
How may I modify it to do the job?


David wrote:
When I specify a column of a combo which resided on a form to be the
criterion, my select query won't pick it up.

Here is the criteria (in design grid):
[Forms]![FormName]![ComboName].Column(1)

Here is the SQL for it (pl ignore my typos, if any):
SELECT [QueryName].FIELD1, [QueryName].FIELD2 INTO [TableName]
FROM [QueryName]
WHERE ((([QueryName].FIELD1)=[Forms]![FormName]![ComboName1].Column(1)

Here is the response from running the Query:
Undefined function in expression .... column(1)

And the ACCESS (local/online) HELP says:
Help says: you entered an SQL expression that includes a Function procedure
name that cannot be recognized.

Does anyone have a solution to this?
(My purpose is to be able to get the query to accept input from the form's
combo box.)


:
I can't be sure, but I think it's a syntax confilct so the
expression evaluator used in SQL thinks it's a function
(which it can't find). Regardless of why you get the
message, you can not do this by referring to the Xolumn
property.

Instead use a line of code in the combo box's AfterUpdate
event to copy the value to an invisible text box

Me.textbox = ComboName1.Column(1)

and then use the text box in the query's criteria.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top