Date Comparisons

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access aplication that currently uses the following code to
determine the previous week-ending date so it can copy some of the user's
input into a new record:

'assigns the datLastWeek variable to exactly one week before the pop-up
calendar selection for report's week-end date
datLastWeek = DateAdd("ww", -1, datWeekEndDate)

'assigns the datPrevWeekEnd variable to datLastWeek variable's week-ending
date (Saturday)
datPrevWeekEnd = DateAdd("d", (7 - Weekday(datLastWeek)), datLastWeek)

I NEED TO CHANGE THE CODE to find the most recent record. It doesn't matter
if it was a week ago or a month ago, just the date that's nearest the current
date. Do you have any suggestions on how I can do that?
 
Thanks for the quick reply, Alex.
I'm not as experienced with VBA as I probably should be, so I'm still at a
loss with your suggestion.

Right now, I use the dlookup function pasted below to find the matching
record with the specific previous week-ending date.

intLastReportNum = DLookup ("[SBModuleReportID]",
"tblSBWeeklyReportForModule", "[SBModuleName] = '" & strModuleName & "'" &
"AND" & "[SBWeekEndingDate] = #" & datPrevWeekEnd & "#" & "AND" &
"[SBBusinessName] is null")

How can I use the MAX function to locate the record with the most recent
date instead of the previous week-ending date (datPrevWeekEnd)? Again I
apologize for my ignorance, and appreciate greatly any direction you
provide!!!

--
Thanks!
Mona-ABE


Alex Dybenko said:
Hi,
you can try to use Max() function:
http://alexdyb.blogspot.com/2006/04/min-and-max-functions.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
Click to expand...
 
Hi,
as I understand you -
after you got these two:

datLastWeek = DateAdd("ww", -1, datWeekEndDate)
datPrevWeekEnd = DateAdd("d", (7 - Weekday(datLastWeek)), datLastWeek)

then you can find most recent date as
datMostRecent=Max(datPrevWeekEnd,datLastWeek )

and then use your DLookup based on datMostRecent


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Mona-ABE said:
Thanks for the quick reply, Alex.
I'm not as experienced with VBA as I probably should be, so I'm still at a
loss with your suggestion.

Right now, I use the dlookup function pasted below to find the matching
record with the specific previous week-ending date.

intLastReportNum = DLookup ("[SBModuleReportID]",
"tblSBWeeklyReportForModule", "[SBModuleName] = '" & strModuleName & "'" &
"AND" & "[SBWeekEndingDate] = #" & datPrevWeekEnd & "#" & "AND" &
"[SBBusinessName] is null")

How can I use the MAX function to locate the record with the most recent
date instead of the previous week-ending date (datPrevWeekEnd)? Again I
apologize for my ignorance, and appreciate greatly any direction you
provide!!!
Click to expand...
 
Back
Top