This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(166.4, 27%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
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?
Hi,
I see these potential and certain causes:
Try this:
DMax("[Milestone Date]","MilestoneTable","idrProjectID=" & [idrProjectID] & " AND [Milestone Date]<" & CLng([Milestone Date]))
Servus
Karl
Access News
Access DevCon
Karl, I appreciate the help and I have addressed your points. However, I still receive an #ERROR. I also took the liberty to try a CDate([Milestone Date]) with the same results. Any more thoughts. Being able to pull the most recent event date of a project would be great.
Ian
Karl, I have resolved the issues using a subquery. I used http://allenbrowne.com/subquery-01.html for help. However, I had to delete 2 rows of data which were causing multiple records results in order to get this to work. I suspect that adding your corrections would have worked too because of this issue. This is why I will mark your answer as correct. Also, Mr. Sheridon, thank for your assistance as well.
Ian
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") & "#")