CODEWITHSUNDEEP
ms-access
Fraz Sundal
Fraz Sundal

Reputation: 10448

How to declare and set variable in ms access 2007 query

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'

Upvotes: 5

Views: 46593

Answers (2)

mwolfe02
mwolfe02

Reputation: 24207

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

Upvotes: 2

Thomas
Thomas

Reputation: 64645

If by "in a query" you mean "without resorting to a VBA routine", then it is trickier. The simplest solution is a single query which you then use in the main query. The query that gets the values might look like:

Select SomeCol As Var1
    , ( Select SomeOtherCol
        From SomeOtherTable ) As Var2 
From SomeTable

The above assumes that the subquery will return a single row and that we get a row for the value of Var1. If that was not guaranteed, then you would want to use an outer query guaranteed to get return one row like so:

Select (Select SomeCol
            From SomeTable ) As Var1
    , ( Select SomeOtherCol
        From SomeOtherTable ) As Var2 
From AnotherTableWithAtLeastOneRow
Where Col = "SomeValueToGetOneRow"

You would save that as a query definition and then use it in your main query.

Upvotes: 2

Related Questions