Solved

MS Access 2003 VBA Function

Posted on 2013-02-04
7
601 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

749 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