This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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") & "#")
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