Converting weekend date to weekday date

[nmc1975]

Hi,

I would like to run an update query in Access to update a column called "NewDate" with the following Monday date, if the Date column shows Saturday or Sunday.

Example: if Date = Weekend date, then update NewDate to the following Monday date

What should I enter as my expression criteria?

Fields:
Date
NewDate

nmc1975

 

[braindead2]

newdate:choose(weekday([date]),dateadd("d",1,[date]),[date],[date],[date],[date],[date],dateadd("d",2,[date]))

 

[MichaelRed]

While I would not dispute the results, I would NOT like to find braindead's little gen buried in 'STUFF on a dank and dreary night of things that go bump-awry in the dark. A slightly easier to digest function, which should accomplish the same / similar results:

Public Function basWkEnd2Mon(DateIn As Date) As Date

    'Michael Red, 12/14/2001 Forces a 'weekend' date t the following Monday
    'Sample Usage (run on Friday, 12/14/2001):
    '? basWkEnd2Mon(DateAdd("d", 1, date))
    '12/17/01

    '? basWkEnd2Mon(DateAdd("d", 2, date))
    '12/17/01


    Select Case Weekday(DateIn)

        Case Is = 1     'Sunday
            basWkEnd2Mon = DateAdd("d", 1, DateIn)

        Case Is = 7     'Saturday
            basWkEnd2Mon = DateAdd("d", 2, DateIn)

        Case Else
            basWkEnd2Mon = DateIn

    End Select

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[braindead2]

Michael,
I am distressed that you failed to recognize the beauty of "my little gem"! In my defense. nmc175 asked

"What should I enter as my expression criteria?"

While your solution provides greater reusability it would require the user create an additional function and then that be placed in the criteria. I selected an internal function and adapted it to meet his request.
No offense to nmc175 or others users of this forum but often when a user created function is posted, the next reply is "Where do I place that"????
I personally like the choose() function and find it easily digestable.
But I also must admit were it for my own use I would have used a solution more similar to yours.

 

[MichaelRed]

Alas and alacy awry is the way of the world of posting. It was MOSTLY this very line which prompted me to add my little bits. If 'they' need this much instruction, then 'they' wouls likely be concerned over the obscurity ... after all, HOW cound anyone need all of thse dates!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over