Starting a date to every Monday 1

[jagnval]

We are designing a database that will help us get rid of unneeded items at our facility, kind of like an e-bay site for out company. I would like to make things simple by starting all bids on Mondays and ending on Fridays, so when someone enters in information about an item to put up for bid, no matter what date they enter it in on, I would like for the "start date" to go to the Next Monday after they enter the info. What function or code would i use to do so?

 

[dhookom]

I would create a small function in a module named "modDateCalcs". You will be able to use this function almost everywhere you would use any other function.

Public Function GetNextWeekday(datDate As Date, _
        intDOW As Integer) As Date
[green]    'datDate is the reference date
    'intDOW = 1 for Sunday, 2 for Monday, 3 for Tuesday etc[/green]
    Dim intI As Integer
    datDate = datDate + 1  [green]'ignore the initial date[/green]
    Do Until Weekday(datDate) = intDOW
        datDate = datDate + 1
    Loop
    GetNextWeekday = datDate
End Function

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Duane:

Why loop

how about
Public Function GetNextWeekday(datDate As Date, _
intDOW As Integer) As Date
'datDate is the reference date
'intDOW = 1 for Sunday, 2 for Monday, 3 for Tuesday etc

GetNextWeekday=Dateadd("d",7,datDate - weekday(#8/25/9#)+intDOW )

end function

 

[TheAceMan1]

How are ya jagnval . . .

Try this:

[blue]Public Function NextMon(usrDate As Date) As Date
   
   NextMon = usrDate + Choose(Weekday(usrDate), 1, 7, 6, 5, 4, 3, 2)
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PWise]

soory SB

Public Function GetNextWeekday(datDate As Date, _
        intDOW As Integer) As Date
'datDate is the reference date
    'intDOW = 1 for Sunday, 2 for Monday, 3 for Tuesday etc
 
GetNextWeekday=Dateadd("d",7,datDate - weekday(#datDate)intDOW )

end function

 

[dhookom]

I used a loop because I think it would be easier for someone to follow the logic. Plus it was easier on my brain on a Monday morning ;-)

Duane
Hook'D on Access
MS Access MVP

 

[jagnval]

I am new to VBA code, and by that I mean, I have self taught myself by trail and error (a lot of error) just copying code and hopes it works, so I need someone to tell me exactly what i need to do. I have a form called "Enter Auction Item Details" with a field " Start Bid Date" What i need is when someone enters in an item the start date is defaulted to Today "Date()" on the "after update prop." I want to run the function "GetNextWeekday" how do I call the function out. Please help. thank you to all who replied.

 

[PWise]

Put a text box on the form called NextWeekDay
in the after update of Start Bid Date

me.NextWeekDay=GetNextWeekday(me.[Start Bid Date] As Date, _
        2 As Integer)

 

[dhookom]

You should use the "AS" in the function call. Try:

Me.NextWeekDay=GetNextWeekday(Me.[Start Bid Date],2)

Duane
Hook'D on Access
MS Access MVP