Get the weekending date from today's date

[Huddle]

I'm trying to create a formula to retrieve the final day of the week depending upon the day the report is open. Here is the formula I used when it was in an excel sheet, where A1 is today's date. Can you tell me how to do this in Access?

=IF(WEEKDAY(A1)=7,A1,A1+(7-WEEKDAY(A1)))

[Bob Fitz]

Take a look at the attached db. It has many useful date calculations in it.

[Huddle]

it's not allowing me to use the database due to version conflicts

[Bob Fitz]

Have you unzipped it. I know it seems a silly question to ask but I am using the same version as you.

[Huddle]

Yes and it opened but it popped up with errors and then went to the debugging screen

[Bob Fitz]

Ok. Try this version:

[Huddle]

it says I'm "missing or broken reference to the file MSCALOCX version 7.0. I'll click on it several time and it will open to the form screen. Then when I click on the form it tells me missing reference to 'utility.mda'. It give me the first error again. Then the form comes up with data in it, then it opens to the debugger.

[Bob Fitz]

This is the function is used in that db.

Function tbLastDateW(dtmDate)
    '
    'Warning dtmDate must be a valid date
    '
    'Return the Last date of provided week
    '
    Dim intDayCnt As Integer
    
    intDayCnt = 7 - WeekDay(dtmDate, 1)
    tbLastDateW = DateAdd("d", intDayCnt, dtmDate)
End Function

[Huddle]

Thanks. I'll work on it next week. Have a great weekend

[Bob Fitz]

Your welcome. Good luck

[Huddle]

Here is the expression for Friday: Date() - WeekDay(Date()) + 7 for Saturday: Date() - WeekDay(Date()) + 8