Use fields on form as a criterium for a query?

[Henrootje]

I have a form that gets filtered on several fields but the OKGR_ID is
not one of them.
OKGR_ID is a numeric field.
I would like to use those values in a query

Select *

>From MyQuery

Where (the Groupnumber equals one of the values mentioned on the form
in the field OKGR_ID)

Any suggestions as to how I can do this?

[Henrootje]

Henrootje schreef:

I have a form that gets filtered on several fields but the OKGR_ID is
not one of them.
OKGR_ID is a numeric field.
I would like to use those values in a query

Select *

From MyQuery

Where (the Groupnumber equals one of the values mentioned on the form
in the field OKGR_ID)

Any suggestions as to how I can do this?

I am rereading this and realising I am not very clear... I have a form
that gets filtered in different ways, based on different fields. One of
the fields always shown is OKGR_ID, which contains a numeric value. So
after filtering I might have 6 rows, next time 14, after that several
100 and so on and so on.

Now I have a query that contains the field GroupNumber which is numeric
too and of the same format and length. In fact they are the same except
for their names.

Now I would like to use all the number shown on my form to be used as
criteria in my query.

Select *

>From MyQuery

Where (the Groupnumber equals one of the values mentioned on the form
in the field OKGR_ID)

I found some code here in a thread with title 'use values from FORM in
query "IN" statement' but that is too complicated for me to understand
:s Is there anyuone able and willing to help me?

[Ron2006]

Whatever query you are using to show the numbers on the from needs to
be used in your new query. If the query is not specifically saved when
you look at the control in design view for that control item on the
form, then open that up and then save it as a real saved query, then
write your new query that has the just saved query as one of the
files/queries in it and that matches records based on the numbers in
that query.

Ron

[Bas Cost Budde]

The latter part, "where the number is one of a set" is best done using
an INNER JOIN between your table and the set of numbers.

What is not clear to me, still, is how you arrive at your set of
numbers. If explaining yourself in Dutch seems a helpful step, be my
guest, do so at bas apestaartje heuveltop punt ennel (now would any
spambot see that as a valid mailadress? :D)

The general idea though in that case is formulated well by Ron2006. Get
a saved query and join to that.

--
Bas Cost Budde
Holland

[Henrootje]

Thanks for thinking with me but I have it solved!!!
I found the following code and adapted it to my needs.
After defining the querysyntax, I created a temp query (from code) end
using that sql syntax to transferspreadsheet the found data!

SQL = "select mytable.* from mytable where Fieldname in ("
Dim RS As Recordset: Set RS = Forms!Formnae.RecordsetClone
If RS.RecordCount 0 Then RS.MoveFirst
While RS.EOF = False
SQL = SQL & Str$(RS!Fieldname) & ","
RS.MoveNext
Wend
Set RS = Nothing
If Right(SQL, 1) = ")" Then
SQL = SQL & "null)"
Else
Mid(SQL, Len(SQL), 1) = ")"
End If

But now I want to adapt one more thing but that won't work:
I want this part Str$(RS!Fieldname) to be dynamic:

Using something like:

Dim sColumn
Dim Fieldname

sColumn = Fieldname
sFieldname= "Str$(RS!" & sColumn & ")"

This

So if I want to reuse this code with a different field I can simply
change the "sColumn =" ..
But if I do it as shown above gets added to the query as a string :
SQL = SQL & "Str$(RS!Fieldname)" & ","
How do I prevent that sFieldname is considered a string?
Bas Cost Budde schreef:

The latter part, "where the number is one of a set" is best done using
an INNER JOIN between your table and the set of numbers.

What is not clear to me, still, is how you arrive at your set of
numbers. If explaining yourself in Dutch seems a helpful step, be my
guest, do so at bas apestaartje heuveltop punt ennel (now would any
spambot see that as a valid mailadress? :D)

The general idea though in that case is formulated well by Ron2006. Get
a saved query and join to that.

--
Bas Cost Budde
Holland

[Bas Cost Budde]

SQL = SQL & "Str$(RS!Fieldname)" & ","

How do I prevent that sFieldname is considered a string?

SQL = SQL & "str(rs(" & sFieldname & "),"
--
Bas Cost Budde
Holland

[Bas Cost Budde]

Bas Cost Budde schreef:

>SQL = SQL & "Str$(RS!Fieldname)" & ","
How do I prevent that sFieldname is considered a string?

I mean

SQL = SQL & "str(rs(" & sFieldname & ")),"

(that's 2 closing brackets)
--
Bas Cost Budde
Holland

[Henrootje]

Dank je Bas!

Thank you Bas

Bas Cost Budde schreef:

Bas Cost Budde schreef:

SQL = SQL & "Str$(RS!Fieldname)" & ","
How do I prevent that sFieldname is considered a string?

I mean

SQL = SQL & "str(rs(" & sFieldname & ")),"

(that's 2 closing brackets)
--
Bas Cost Budde
Holland