CODEWITHSUNDEEP
ms-accessvba
BusterS
BusterS

Reputation: 11

VBA cannot find date field from query

I have created a query OrderAllQuery from 3 related tables. One of the fields is a date field that has the same name in the original table and the query named OrdDate.

I have a simple sub to run the query and then run a report for a specific date range. OrdDate must fall within the range. The sub (see below) shows a 2465 on the Odate = line.

Can't see the problem. Any advice would be welcome

Private Sub cmdOrders_Click()
    DoCmd.OpenQuery "OrderAllQuery"
    Dim Odate As Date
    Odate = [OrderAllQuery.OrdDate]
    DoCmd.Close acQuery, "OrderAllQuery"

    Call RunReport("Orders Report", Odate, Me.txtDateFrom, Me.txtDateTo)
End Sub

Upvotes: 1

Views: 47

Answers (1)

Lee Mac
Lee Mac

Reputation: 16025

Note that the OpenQuery method of the DoCmd object will merely open a query of the given name in datasheet view (assuming that the query is not an action query, and the view argument has not been specified); you cannot then obtain data directly from the datasheet view through your code.

To obtain a value from the query, you could either open the query as a DAO or ADO recordset, e.g.: (using DAO):

Private Sub cmdOrders_Click()
    With CurrentDb.OpenRecordset("OrderAllQuery")
        If Not .EOF Then
            .MoveFirst
            Call RunReport("Orders Report", !OrdDate, Me.txtDateFrom, Me.txtDateTo)
        End If
        .Close
    End With
End Sub

Or use a domain aggregate function such as DLookup or DFirst if you are indifferent about which record is returned:

Private Sub cmdOrders_Click()
    Dim odate As Date
    odate = DFirst("OrdDate", "OrderAllQuery")

    If Not IsNull(odate) Then
        Call RunReport("Orders Report", odate, Me.txtDateFrom, Me.txtDateTo)
    End If
End Sub

Upvotes: 1

Related Questions