[RESOLVED] question about Date and DateAdd.

[lok1234]

Dear all,

I have the following Pseudo code and my questions are include in the remark: 'how to write this? and RED in color

Sorry for not be able to ask my questions clearly and thank you for your help in advance.

public function fn_matched_date(p_hit_weekday as string, _ 
                                p_control_Date as date, p_Input_Date as date) as date

Dim isExist as boolean

'p_Hit_Weekday=2,6 'Wednesday and Friday
'p_control_Date=#01-Jul-2009#
'p_Input_Date=#21-Apr-2009#

while p_input_date <= p_control_Date
  if p_input_date is in (p_hit_weekday) then 'How to write this?
     'Check if p_input_date is Wed or friday which are the values in the variable p_Hit_weekday

     isExist = fn_Date_Exist(p_input_date)
     'Check if this date exists in a database table by an existing function: fn_date_exists     
  else
     p_input_date=add to the next nearest hit weekday 'How to write this?
   '1st e.g. if p_input_date is thursday, it need to add one date so that it will become friday
   '2nd e.g. if p_input_date is monday, it need to add two dates so that it will become Wednesday
   '3rd e.g. if the variable p_hit_weeday is having the values: 3,4 , and if p_input_date is monday, it need to add three dates so that it will become Thursday

     isExist = fn_Date_Exist(p_input_date)
  end if

   if isExist then
      fn_matched_date = p_input_date
      exist the while loop
   else
      p_input_date=add to the next nearest hit weekday
      'to continue the looping until it find the matched date
   end if

wend

  if not isExist then
     fn_matched_date = #31/12/1899#         
  end if
  ' return an invalid date in case it cannot find the matched date

End function

[brucevde]

Change from a While Wend to a Do While Loop (or Do Until Loop) because you can exit out of a Do Loop with a simple Exit Do statement. There isn't an Exit While statement although simply changing the looping value so that the While condition is False is simple enough. Note though the Do...Loop is recommended over While...Wend

Use the Instr function to check if a value appears in a string.
Use the Weekday function on a Date to "Return a whole number representing the day of the week"

To "find the next nearest hit weekday", I would use another Do Loop that keeps on incrementing the input date by 1 day until a match is found.

Public Function fn_matched_date(p_hit_weekday As String, p_control_Date As Date, p_Input_Date As Date) As Date

    'Set the function's default return value
    fn_matched_date = #12/31/1899#

    Do While p_Input_Date <= p_control_Date
        'Check if the Weekday number of the Input Date is in the 
        'list of weekdays passed to the function.
        Do Until InStr(1, p_hit_weekday, Weekday(p_Input_Date)) > 0
           'the weekday of input date is not in p_hit_weekday list
           'increment p_input_date by 1 day
           p_Input_Date = DateAdd("d", 1, p_Input_Date)
        Loop
        
        If fn_Date_Exist(p_Input_Date) then
           fn_matched_date = p_Input_Date
           Exit Do
        End If

        'increment the input date by 1 day
        p_Input_Date = DateAdd("d", 1, p_Input_Date)
    Loop
End Function

You should add some code to verify the value of p_hit_weekday. If it contains invalid data (like "a,b,c,") the Do Until loop will never exit.

Actually, using a ParamArray instead of a String for the "hit weekday" argument might be a better option.

Public Function fn_matched_date(p_control_Date As Date, p_Input_Date As Date, ParamArray WeekDays() as Variant) As Date

[lok1234]

Change from a While Wend to a Do While Loop (or Do Until Loop) because you can exit out of a Do Loop with a simple Exit Do statement. There isn't an Exit While statement although simply changing the looping value so that the While condition is False is simple enough. Note though the Do...Loop is recommended over While...Wend

Use the Instr function to check if a value appears in a string.
Use the Weekday function on a Date to "Return a whole number representing the day of the week"

To "find the next nearest hit weekday", I would use another Do Loop that keeps on incrementing the input date by 1 day until a match is found.

Public Function fn_matched_date(p_hit_weekday As String, p_control_Date As Date, p_Input_Date As Date) As Date

    'Set the function's default return value
    fn_matched_date = #12/31/1899#

    Do While p_Input_Date <= p_control_Date
        'Check if the Weekday number of the Input Date is in the 
        'list of weekdays passed to the function.
        Do Until InStr(1, p_hit_weekday, Weekday(p_Input_Date)) > 0
           'the weekday of input date is not in p_hit_weekday list
           'increment p_input_date by 1 day
           p_Input_Date = DateAdd("d", 1, p_Input_Date)
        Loop
        
        If fn_Date_Exist(p_Input_Date) then
           fn_matched_date = p_Input_Date
           Exit Do
        End If

        'increment the input date by 1 day
        p_Input_Date = DateAdd("d", 1, p_Input_Date)
    Loop
End Function

You should add some code to verify the value of p_hit_weekday. If it contains invalid data (like "a,b,c,") the Do Until loop will never exit.

Actually, using a ParamArray instead of a String for the "hit weekday" argument might be a better option.

Public Function fn_matched_date(p_control_Date As Date, p_Input_Date As Date, ParamArray WeekDays() as Variant) As Date

good direction to me many thanks. Let me try

[brucevde]

What was I thinking!!! Forget about using that Inner Do Loop, it is not needed. A simple If statement is enough.

Public Function fn_matched_date(p_hit_weekday As String, p_control_Date As Date, p_Input_Date As Date) As Date

    'Set the function's default return value
    fn_matched_date = #12/31/1899#

    Do While p_Input_Date <= p_control_Date
        'Check if the Weekday number of the Input Date is in the 
        'list of weekdays passed to the function.
        If InStr(1, p_hit_weekday, Weekday(p_Input_Date)) > 0 Then
            If fn_Date_Exist(p_Input_Date) then
               fn_matched_date = p_Input_Date
               Exit Do
            End If
        End If

       'the weekday of input date is not in p_hit_weekday list
       'or the date does not exist
       'increment p_input_date by 1 day
        p_Input_Date = DateAdd("d", 1, p_Input_Date)
    Loop
End Function

[lok1234]

What was I thinking!!! Forget about using that Inner Do Loop, it is not needed. A simple If statement is enough.

Public Function fn_matched_date(p_hit_weekday As String, p_control_Date As Date, p_Input_Date As Date) As Date

    'Set the function's default return value
    fn_matched_date = #12/31/1899#

    Do While p_Input_Date <= p_control_Date
        'Check if the Weekday number of the Input Date is in the 
        'list of weekdays passed to the function.
        If InStr(1, p_hit_weekday, Weekday(p_Input_Date)) > 0 Then
            If fn_Date_Exist(p_Input_Date) then
               fn_matched_date = p_Input_Date
               Exit Do
            End If
        End If

       'the weekday of input date is not in p_hit_weekday list
       'or the date does not exist
       'increment p_input_date by 1 day
        p_Input_Date = DateAdd("d", 1, p_Input_Date)
    Loop
End Function

thanks