How to declare and set variable in ms access 2007 query

Question

I want to declare and set variables in ms access 2007 database query. I want to store 2 database query results in 2 variables because they are integer or string types after execution. I want to know how can I declare and set variables?

SQL server equivalent would be something like this

declare @var1 varchar(50)
set @var1 = 'select * from table'

Answer 1

There is no support for this syntax in Jet/ACE SQL. Depending on what your ultimate goal is here, you will need to use either VBA (sample provided below) or subqueries (as in @Thomas's solution) for this type of functionality.

Something along the following lines (adapted from Allen Browne's website):

Function DAORecordsetExample()
    'Purpose:   How to open a recordset and loop through the records.'
    'Note:      Requires a table named MyTable, with a field named MyField.'
    Dim rs As DAO.Recordset
    Dim strSql As String

    strSql = "SELECT MyField FROM MyTable;"
    Set rs = CurrentDb.OpenRecordset(strSql)

    Do While Not rs.EOF
        Debug.Print rs!MyField
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
End Function