Previous Date in access query

imodett 41 Reputation points

I am attempting to pull the previous date of an event of the same Project in a query using

DMax("[Mikestone Date]", "[MilestoneTable]", "[idrProjectID] = " & [idrProjectID] & " AND [Milestone Date] < [Milestone Date])

However, I keep coming up with #ERROR. Can anyone assist?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
817 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karl Donaubauer 1,646 Reputation points MVP


    I see these potential and certain causes:

    1. There seems to be a typo in the first field name of your DMax expression: Mikestone --> Milestone
    2. There is a " missing after the <.
    3. The value for the date criteria has to be in the right format.

    Try this:

    DMax("[Milestone Date]","MilestoneTable","idrProjectID=" & [idrProjectID] & " AND [Milestone Date]<" & CLng([Milestone Date]))

    Access News
    Access DevCon

1 additional answer

Sort by: Most helpful
  1. Ken Sheridan 2,666 Reputation points


    DMax("[Milestone Date]", "[MilestoneTable]", "[idrProjectID] = " & [idrProjectID] & " AND [Milestone Date] < #" & Format([Milestone Date],"yyyy-mm-dd") & "#")

    The use of the ISO standard for date notation of YYYY-MM-DD in the date literal removes any chance of international ambiguity.

    Is idrProjectID a number data type? If it's text use:

    DMax("[Milestone Date]", "[MilestoneTable]", "[idrProjectID] = """ & [idrProjectID] & """ AND [Milestone Date] < #" & Format([Milestone Date],"yyyy-mm-dd") & "#")

    0 comments No comments