Txt box date start Mon current week

[Chad]

Hello, Is this possible? I have a text box I use for a date picker. The text
box name is txtStartDate. Is it possible to have VBA in the forms On Load
Event that lets say I opened the form and today was saturday then the
txtStartDate box would show mondays date. Would the VBA start out like some
like:

Me.txtStartDate = Date?????

Thanks,
Chad

 

[Duane Hookom]

You should be able to set the default value to something like:
=DateAdd("d",Choose(Weekday(Date()), 1,0,0,0,0,0,2), Date())

 

[Tom Wickerath]

Hi Chad,

Something like this should work for unbound text boxes:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Dim ThisWeekMonday As Date

ThisWeekMonday = Date - (Weekday(Date) - 2)

Me.txtFindDate = ThisWeekMonday
Me.txtFindContactRecordDate = ThisWeekMonday

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Open..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Steven Hartman]

I used:

=IIf(Format(Date()-1,"ddd")="Mon",Date()-1,IIf(Format(Date()-1,"ddd")="Mon",Date()-1,IIf(Format(Date()-2,"ddd")="Mon",Date()-2,IIf(Format(Date()-3,"ddd")="Mon",Date()-3,IIf(Format(Date()-4,"ddd")="Mon",Date()-4,IIf(Format(Date()-5,"ddd")="Mon",Date()-5,IIf(Format(Date()-6,"ddd")="Mon",Date()-6)))))))

as the default value and it worked for my application. Maybe it will work for you too.

 

[Steven Hartman]

I used:

=IIf(Format(Date(),"ddd")="Mon",Date(),IIf(Format(Date()-1,"ddd")="Mon",Date()-1,IIf(Format(Date()-2,"ddd")="Mon",Date()-2,IIf(Format(Date()-3,"ddd")="Mon",Date()-3,IIf(Format(Date()-4,"ddd")="Mon",Date()-4,IIf(Format(Date()-5,"ddd")="Mon",Date()-5,IIf(Format(Date()-6,"ddd")="Mon",Date()-6)))))))

as the default value and it worked for my application. Maybe it will work for you too.

 

[bcap]

I've no idea what question you are replying to, but you will find lots of
clever (and concise!) date manipulations here:

http://support.microsoft.com/kb/88657/en-us

For example, a minor adaptation to one of the suggestions on that page gives
the following for finding the previous Monday:

Day(Date() - WeekDay(Date(), 2) + 1)