Solved

MS Access 2003 VBA Function

Posted on 2013-02-04
7
544 Views
Last Modified: 2013-02-04
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
Comment
Question by:lrbrister
  • 3
  • 2
  • 2
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38851899
Is this a homework assignment?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38851906
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
 

Author Comment

by:lrbrister
ID: 38851938
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Expert Comment

by:jerryb30
ID: 38851966
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38852048
If you are trying to get only date, change any reference to now() to date().
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38852072
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
 

Author Closing Comment

by:lrbrister
ID: 38852930
Works great. Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now