Setting a date to Saturday in a textbox.

[chanman525]

Another question concerning dates.

I have to find the Saturday and Friday from 2 weeks out, last week, this week, next week and the following.

I'm looking at writing code for each week to say

if date() = Monday then
date -2

but I'm having trouble displaying it in the textbox (txt2WeeksAgoSaturday) in my form.

My first question is, is there an easier way to find Saturday's. This is something that will be run everyday, so I can't just put -1 or -4 depending on what day they run. Unless I have to write code for each day of the week.

My second question is, how do I get this to show up in my textbox?

 

[stix4t2]

The DateAdd routine is normally used to change relative position of a date. You first have to isolate the current day and its relative position to Saturday, use the Day() function. Then by using the difference, you can use the DateAdd routine to get the Saturday you are looking for, expressed as a date. You should be able to calculate relative difference for each scenario you are seeking.

 

[PHV]

Have a look at the WeekDay function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stix4t2]

Good call PHV, it is the WeekDay() function

 

[MajP]

3 weeks out
txtBoxSat = fncNextSat(3)
2 weeks out
txtBoxSat = fncNextSat(2)

You could add another parameter and then you could write a function to find any day.

Public Function fncNextSat(intWeeksOut As Integer) As Date
  Dim intCounter As Integer
  Dim dtmDate As Date
  dtmDate = date
  If intWeeksOut < 1 Then
    MsgBox "Must look at least 1 week out"
    Exit Function
  End If
  For intCounter = 1 To intWeeksOut
    Do Until Weekday(dtmDate) = [b]7[/b]
      dtmDate = dtmDate + 1
    Loop
    dtmDate = dtmDate + 1
 Next intCounter
 fncNextSat = dtmDate - 1
End Function

 

[MajP]

Saturday, 3 weeks out
txtBoxSat = fncNextDay(3,7)

Friday 2 weeks out
txtBoxSat = fncNextDay(2,6)

Public Function fncNextDay(intWeeksOut As Integer, intDayOfWeek As Integer) As Date
  Dim intCounter As Integer
  Dim dtmDate As Date
  dtmDate = date
  If intWeeksOut < 1 Then
    MsgBox "Must look at least 1 week out"
    Exit Function
  End If
  For intCounter = 1 To intWeeksOut
    Do Until Weekday(dtmDate) = intDayOfWeek
      dtmDate = dtmDate + 1
    Loop
    dtmDate = dtmDate + 1
 Next intCounter
 fncNextDay = dtmDate - 1
End Function

 

[MajP]

Public Function fncNextDay(intWeeksOut As Integer, intDayOfweek As VbDayOfWeek) As Date

This will allow you to use the intellisense.

 

[SirTECH]

Try this.
Based on the current date, dtDATE becomes the Day-of-week date using the intDOW value, where 1 = Monday, 2 = Tuesday, etc.
Then intTARGET represents the number of days from the dtDATE (Day-of-week date). An intTARGET value of -7 represents seven days prior to dtDATE, and an intTARGET value of 7 represents seven days after dtDATE.

Public Function TargetDATE(intDOW As Integer, intTARGET As Integer) As Date
Dim dtDATE As Date
dtDATE = Date - Weekday(Date, vbMonday) + intDOW
TargetDATE = DateAdd("d", intTARGET, dtDATE)
End Function

 

[MajP]

Yeah I was driving home and thought that boy was that some inefficient code. How about

Public Function fncNextDay(intWeeksOut As Integer, intDayOfWeek As Integer) As Date
  fncNextDay = 7 * intWeeksOut + (Date + (intDayOfWeek - WeekDay(Date)))
End Function

this thursday
fncNextDay(0,5)

2 saturdays ago
fncNextDay(-2,7)

8 Fridays from now
fncNextDay(8,6)

Sorry about that.