Query criteria

  • Thread starter Thread starter Pegawars
  • Start date Start date
P

Pegawars

I want to use the value of colomn 4 of a listbox as a criteria in a query.

What is the notation of that criteria?

One not working example i used was [forms]![formname]![listbox1.column(4)]

When i put the value of column 4 in a text box and used the txtbox name in
the criteria it was working properly.

[forms]![formname]![txtbox]

Please help.

PeGaWars
 
I would help but you have already found the solution that I use. Set the
control source of txtbox to
=ListBox1.Column(4)
You might also be able to use
Eval([forms]![formname]!listbox1.column(4))
 
I would help but you have already found the solution that I use. Set
the control source of txtbox to
=ListBox1.Column(4)
You might also be able to use
Eval([forms]![formname]!listbox1.column(4))
Click to expand...

Thanx for your help but this is the answer from Ken Snell on a what i am
going to use :

Queries cannot "see" any column in a combo box or list box except the
bound column (because the bound column is the value of that control).

Create a public function that gets the value of the column you want and
returns it to the query. For example, in your query, replace the
[Forms]![frm-Name]![combo1].[column(3)] reference with this:

GetMyColumnValue()

Create a public function in a regular module (name the module
basFunction):

Public Function GetMyColumnValue(lngColumnNumber As Long) As Variant
GetMyColumnValue = Forms]![frm-Name]![combo1].Column(3)
End Function

--


Ken Snell
<MS ACCESS MVP>

Thanx to both off you!!!

I can see it enough :
What are we with out a MPV!!!!!!
 
Note that my solution may cause the query to run a bit slowly (calling a
user-defined function in a query can do that at times), while Duane's
solution of using a textbox on the form will run faster.

--

Ken Snell
<MS ACCESS MVP>

Pegawars said:
I would help but you have already found the solution that I use. Set
the control source of txtbox to
=ListBox1.Column(4)
You might also be able to use
Eval([forms]![formname]!listbox1.column(4))
Click to expand...

Thanx for your help but this is the answer from Ken Snell on a what i am
going to use :

Queries cannot "see" any column in a combo box or list box except the
bound column (because the bound column is the value of that control).

Create a public function that gets the value of the column you want and
returns it to the query. For example, in your query, replace the
[Forms]![frm-Name]![combo1].[column(3)] reference with this:

GetMyColumnValue()

Create a public function in a regular module (name the module
basFunction):

Public Function GetMyColumnValue(lngColumnNumber As Long) As Variant
GetMyColumnValue = Forms]![frm-Name]![combo1].Column(3)
End Function

--


Ken Snell
<MS ACCESS MVP>

Thanx to both off you!!!

I can see it enough :
What are we with out a MPV!!!!!!
Click to expand...
 
Back
Top