MS Access 2003 VBA Function

I need to create a function in MS Access 2003 VBA code that

I pass in a Yes or No

If Yes it returns a date calculated from the previous saturday + 13

If No then  it returns a date calculated from previous saturday + 6 days.
Who is Participating?
Dale FyeConnect With a Mentor Commented:
Do you really want to pass a text string ("Yes", "No")

Note that I changed the return value to a variant, to accommodate a NULL value if you pass the wrong arguments.

Public Function FindPayDate(byVal lag as string) as Variant

    Dim intOffset as integer

    if lag = "Yes" Then
        intOffset = 13
    elseif lag = "No" then
        intOffset = 6
         msgbox "You passed '" & lag & "'!", vbokonly, "Invalid value"
         FindPayDate = NULL
         Exit Function
    End if

    FindPayDate = dateadd("d", -weekday(date(), vbSunday), date()) + intOffset

End Function
Dale FyeCommented:
Is this a homework assignment?
Dale FyeCommented:
To get the "previous saturday", I assume you mean from the current date, use a combination of the dateadd and weekday functions.

?dateadd("d", -weekday(date(), vbSunday), date())

You should be able to take it from there.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

lrbristerAuthor Commented:
NO it is not a homework assignment.

I'm busy and I use EE as a resource.

This is for a hours entry "pay period" function.

Is what I have here a problem?

I modified it with your code and it ran with same results.

Private Function FindPayDate(ByVal lag As String) As Date
    If lag = "Yes" Then
        FindPayDate = (Now() - 7) + (7 - Weekday(Now())) + 13
        FindPayDate = (Now() - 7) + (7 - Weekday(Now())) + 6
    End If
End Function

Open in new window

I'm calculating next time person gets paid based on current day (previous Saturday) hours entry using a "lag" or paid behind period.
What do you want to see?
Your function returns 2/15/2013 10:15:02 AM or
2/8/2013 10:17:32 AM
If you are trying to get only date, change any reference to now() to date().
lrbristerAuthor Commented:
Works great. Thanks
All Courses

From novice to tech pro — start learning today.