VBA cannot find date field from query

Question

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

Does this give you the value you want? Odate = DLookup("[OrdDate]", "OrderAllQuery") — HansUp
– HansUp, Commented Mar 25, 2019 at 17:52

Lee Mac · Accepted Answer · 2019-03-25 18:15:44Z

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

Collectives™ on Stack Overflow

VBA cannot find date field from query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related