Query criteria will not pick up a combo's column

[David]

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

 

[George Nicholson]

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?

 

[david]

yes.

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.

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

 

[Marshall Barton]

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.

 

[david]

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

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.

 

[Marshall Barton]

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?

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]

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.

 

[david]

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

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.

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.

 

[Marshall Barton]

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?

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.

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.

 

[david]

That works!
Thankz Marsh.
David

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?

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.

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.