Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Starting a date to every Monday 1

Status
Not open for further replies.

jagnval

Technical User
Feb 19, 2009
10
US
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?
 
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.
Code:
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
 
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
 
How are ya jagnval . . .

Try this:
Code:
[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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
soory SB

Code:
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
 
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.
 
Put a text box on the form called NextWeekDay
in the after update of Start Bid Date
Code:
me.NextWeekDay=GetNextWeekday(me.[Start Bid Date] As Date, _
        2 As Integer)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top