[RESOLVED] question about Date and DateAdd.

[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