Flagging Record Modified

[jon6035]

I have the following IIF statement working in SQL.
ModFlag: IIF(DatePart("ww",[DateModified])=DatePart("ww", Date())-1,IIF(DatePart("ww",[DateModified])=DatePart("ww",Date())-1,-1,0))

I want to make it a VBA procedure so I can call it, by passing a Date to the Function, but I'm missing something simple.

Currently I have this:
Public Function RecModFlag(ModDate as Date)
RecModFlag = IIF(DatePart("ww",ModDate)=DatePart("ww", Date())-1,IIF(DatePart("ww",ModDate)=DatePart("ww",Date())-1,-1,0))

End Function

Trying to troubleshoot, when I do the DatePart on ModDate in the immediate window, I get 52, when put # sign around it, I get the appropriate week number.

End goal, is all of my records have the last time they were modified, and I want to Conditionally Format a box, that will flag if they have been modified, either this week, or the past week.

Thanks in advance for any help or insight.

 

[dhookom]

I would use something like this which takes advantage of DateDiff():

Public Function RecModFlag(ModDate As Date) As Boolean
    [COLOR=#4E9A06]'return true if the ModDate is either this week or last week[/color]
    RecModFlag = (DateDiff("ww", ModDate, Date) <= 1)
End Function

Duane
Hook'D on Access
MS Access MVP

 

[jon6035]

Sir,

Thank you for the quick, accurate response, and it is definitely cleaner than my attempt.

Worked first time up.

Semper Perceptum,

jon

 

[Andrzejek]

jon6035, welcome to Tek Tips
It is customary to mark helpful post(s) with a star. To do that, click on [blue]“Like this post – Star It’[/blue] link. It serves two purposes: says ‘Thank you for helpful post’, and let others know this was something that solved your problem or at least was beneficial to know.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.