selecting fields from query

[humer]

in the next code i want to select from the query 'SPTrue' all the fields and apend it to the string criteria. executing this is an error that reports to that "an object value is required". i know that my problems is in the for condition but i don't know how to made it
can somebody to help me with this problem ???

    Dim Criteria As String
    Dim count As Integer
    Dim Msg As String
    Dim varItem As Variant
    
    Criteria = ""  
  
    For Each varItem In Querys!SPTrue![Fin_Program_ID]
        Criteria = Criteria & "[Fin_Program_ID] = '" & Queries!SPTrue![Fin_Program_ID] & "' OR "
    Next
    
    If Criteria <> "" Then
        Criteria = Left(Criteria, Len(Criteria) - 4)
    Else
        MsgBox "No one program was selected"
        Exit Sub
     End If

DoCmd.OpenReport "Projects", acPreview, , Criteria

thanks
best regard !

 

[Mile-O]

You can't reference the Query collection the same way as you can with the form collection - forms always show just one record (even if it looks like they can have more than one; one, still, has the focus). Queries, can return thousands of records so referring to a field in a query when you don't know which row to look in is, ultimately, useless.

To access the Queries collection you would use this syntax:

CurrentDb.QueryDefs("QueryName").Fields("FieldName")

I don't think that's what you want though; so, can you be more specific about what you want?

 

[Kodo]

run this as a normal SQL query and slap the recordset into getrows() then loop through the array and concatenate your string criteria that way.

for i=0 to ubound(myarray,2)
Criteria = Criteria & "[Fin_Program_ID] = '" & myarray(i,0) & "' OR "
next

this assumes you're getting the first field.. (0) change the index(0) accordingly. 1 is the 2nd field, 2 is the third field etc etc.

 

[humer]

ok
i think iwasn't understood. my only poblem is to access the fields from a query, that means than i don't know how to make the condition in the for. below i write the pseudo code

For (variable < number_of _rows)
Criteria = Criteria & "[Fin_Program_ID] = '" & Query!SPTrue!Field[variable] & "' OR "
Next

thanks

 

[humer]

Hello
Again me, I changed a litle a code to make the final result, but I have a problem. The problem is accessing the field 'rst!Fin_Program_ID.Value' : type mismatch. Help me please

    Dim Criteria As String
    Dim rst As Recordset
    Dim sql As String
    
    Criteria = ""
  
    sql = "Select * from Fin_Programs Where Print"
    Set rst = CurrentDb.OpenRecordset(sql)

    If rst.EOF And rst.BOF Then
        MsgBox ("Tabela este goala !!!!")
        Exit Sub
    End If

    While Not rst.EOF And Not rst.BOF
        If rst!Print Then
            Criteria = Criteria & "[Fin_Program_ID] = '" & rst!Fin_Program_ID.Value & "' OR "
        End If
        rst.MoveNext
    Wend

thanks a lot
best regards

 

[Mile-O]

You'd be better using the In clause for you SQL rather than the OR - same idea to build

i.e.

SELECT *
FROM MyTable
WHERE MyField IN (value1, value2, value3, value4);

 

[humer]

I use OR only in the concatenation of the fields. The data is token from the table Fin_Programs and the criteria is Print (Yes/No field).

the problem is if I can access the table field value from the recordset!
--------------
thanks