Previous Date in access query

imodett 41 Reputation points
2022-01-21T04:01:02.127+00:00

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.
825 questions
0 comments
Accepted answer
  1. Karl Donaubauer 1,646 Reputation points MVP
    2022-01-21T12:32:09.547+00:00

    Hi,

    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]))

    Servus
    Karl
    Access News
    Access DevCon

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ken Sheridan 2,671 Reputation points
    2022-01-21T16:33:23.073+00:00

    Try:

    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