Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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.
0
lrbrister
Asked:
lrbrister
  • 3
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
Is this a homework assignment?
0
 
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.
0
 
lrbristerAuthor Commented:
fyed...
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
    Else
        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.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
jerryb30Commented:
What do you want to see?
Your function returns 2/15/2013 10:15:02 AM or
2/8/2013 10:17:32 AM
0
 
jerryb30Commented:
If you are trying to get only date, change any reference to now() to date().
0
 
Dale FyeCommented:
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
    else
         msgbox "You passed '" & lag & "'!", vbokonly, "Invalid value"
         FindPayDate = NULL
         Exit Function
    End if

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

End Function
0
 
lrbristerAuthor Commented:
Works great. Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now