IIf statement, DatePart 1

[wvmbark]

I need an IIf statement for a textbox that:

If today is Monday, returns the previous Friday's date, else return yesterdays date.

IIf(DatePart("w",Date()=2), Date()-3, Date()) returns "Name?"

Please help, thanks!!

Mike

 

[PaulBricker]

Try this
IIf(DatePart("w",Date())=2,Date()-3,Date())


Paul

 

[wvmbark]

I tried that one too; it returns; "Name?"

 

[rdodge]

Do you have "Visual Basic for Applications" in the list of references checkmarked?

To Check, press Alt-F11, then in the VB Editor, goto Tools>References

Look in there, and if it's not checkmarked, you will want to scroll on down and place a checkmark in it.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[DeanWilliams]

Hi,

Are you putting this expression in the ControlSource property? If so the control thinks that the date the expression evaluates to is a field name.

It should be in the Form's OnCurrent or OnLoad events or something similar.

Also the code you've got will return today's date instead of yesterday's. You also don't need all those brackets as you have no arguments.

Should be:

Private Sub Form_Current()
     Text1 = IIf(DatePart("w", Date) = 2, Date - 3,  Date - 1)
End Sub

Hope that helps,

Dean

 

[PaulBricker]

Mike, put an = sign in front of the expression if you are using it in the Control Source for the Textbox. It will work fine with the ='s
=IIf(DatePart("w",Date())=2,Date()-3,Date())

Dean, you can use this expression right in the control source for a textbox but if you leave out the brackets in Date() it will bomb. Then Access thinks you are looking for a field and not today's date.

Paul

 

[DeanWilliams]

Of course, I knew that! (Oh well, it's the end of a long week).

 

[PaulBricker]

I understand.

Paul

 

[OhioSteve]

This does exactly what you requested:

IIf(Weekday(Date())=2,Date()-3,Date()-1)

Remember that this will NOT always return the date of the previous workday. You have forgotten about legal holidays.

 

[wvmbark]

Duh! It's always those little things....

Thanks!!

Mike

 

[rdodge]

In regards to the legal holidays, I have had thoughts of creating my own function for that purpose as currently it stands, the only thing that I saw which addresses holidays and workdays are the Excel Worksheet Add-In Analysis ToolPack functions of Workday and Networkdays.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[OhioSteve]

I actually thought about this thread yesterday evening. Perhaps that means that I need a more active social life In any event, let's assume that you want to include legal holidays. I see three ways to approach the project.

METHOD ONE: a Crosswalk Table

Create a table with two columns, Date1 and Date2. Date1 contains a given date. Date2 contains the date of the previous workday. You could populate the table using Excel. A function like mine should be able to calculate most of the values. Then you could go in and change the values that are effected by holidays. Once you have this table, determining the previous workdate is easy to do with sql.

The advantage of this method is simplicity. However, it is a very crude approach. I think that most serious developers would dislike relying on such a table.

METHOD TWO: The Purist's Approach

For those determined to avoid all hardcoding, you could write a VB program which calculates the previous workday for a given date. Let's assume that you are only concerned about one calender year. Your algorithm would look something like this....

start function

getNYday()
getMLKday()
getPresDay()
getMemDay()
getIndDay()
getLabDay()
getColDay()
getVetsDay()
getThanxDay()
getXmas()

create holidayArray

initialize d1 as 1/1/2003
initialize i as 1
until i=366
put d1 into array1
d2=prevWorkday() of d1
if d2 is in holidayArray
d2=d2-1
end if
end loop

My logic is pretty rough even for pseudocode, but you can see where its going. You will eventually have an array called array1 that is lists each date and the prev workday. Obviously you would also have to write little functions to determine each holiday's date. For example, in getThanxDay() you would have to say "its the third Thursday in November" in VB.

The good thing about this approach is that it avoids hardcoding anything. In a computer science class, this would definitely earn you a good grade. However, it would be terribly time consuming to create. Worse yet, what if your organization suddenly changes its holiday policy? The boss may say "You guys have been such good employees, I'm giving you all the day after thanksgiving off!" Then the dba will start screaming "holidayArray! holidayArray!" and jump out of the window.

METHOD THREE: The Hybrid Approach

Okay, this approach incorporates aspects of both (1) and (2). You should probably do it as a little VB program. You would create a table with hardcoded values. However, the table would only have one row and just a few records. Each record would list a holiday date. To obtain a value, you would input a given date. The machine would calculate the date that was probably the last workday, using logic what we have discussed in previous posts. So now we take that date and check it against the holiday table. If the date is a holiday, we repeat the process until we find a nonholiday. If you prefer arrays over tables, you could obviously just create an array when the function kicked off, using hardcoded values for the holidays.

Remember that with all three approaches, you will need to deal with the problem of border values. For example, if you design it to find values after 1993, what happens if someone inputs a date from 1992?

 

[PaulBricker]

Just so people don't reinvent the wheel, there are a few Functions out there in the FAQ's that already tell you how to test for Holidays and Weekends. Look up one by MichaelRed

Paul

 

[jwigh]

I have a routine for calculating if a date is a workday. This one take care of the Swedish Public holidays. You may have to localise it.

Public Function WrkDay(Indate) As Boolean 'Results in False for Saturday, Sundag och holiday True for others
Dim DayOfWeek, Year, A, B, C, D, E, F As Integer
Dim EasterDay As Date

Select Case DatePart("w", Indate, vbMonday, vbFirstFourDays)
Case 6, 7
WrkDay = False ' Indate is a Saturday or Sunday
Exit Function
Case Else ' Tillsvidare vet vi att det är en Må-Fr

If DatePart("m", Indate, vbMonday, vbFirstFourDays) = 1 And (DatePart("d", Indate, vbMonday, vbFirstFourDays) = 1 Or DatePart("d", Indate, vbMonday, vbFirstFourDays) = 6) Then
WrkDay = False 'Indate is NewYearsDay or Epiphany
Exit Function
End If

If DatePart("m", Indate, vbMonday, vbFirstFourDays) = 5 And DatePart("d", Indate, vbMonday, vbFirstFourDays) = 1 Then
WrkDay = False 'Indate is May Day
Exit Function
End If

If DatePart("m", Indate, vbMonday, vbFirstFourDays) = 12 And (DatePart("d", Indate, vbMonday, vbFirstFourDays) = 24 Or DatePart("d", Indate, vbMonday, vbFirstFourDays) = 25 Or DatePart("d", Indate, vbMonday, vbFirstFourDays) = 26 Or DatePart("d", Indate, vbMonday, vbFirstFourDays) = 31) Then
WrkDay = False 'Indate is Christmas Eve,Christmas Day, BoxingDay or New Year's Eve
Exit Function
End If
' Find EasterDay
Year = DatePart("yyyy", Indate, vbMonday, vbFirstFourDays)


m = 24 'Constant valid until 2199
N = 5 'Constant valid until 2099
A = Year Mod 19
B = Year Mod 4
C = Year Mod 7
D = (19 * A + m) Mod 30
E = (2 * B + 4 * C + 6 * D + N) Mod 7
F = 22 + D + E
If F = 57 Or (F = 56 And E = 6 And A > 10) Then F = F - 7

If F <= 31 Then
EasterDay = CDate(Year & &quot;-&quot; & &quot;03-&quot; & F)

Else
EasterDay = CDate(Year & &quot;-&quot; & &quot;04-&quot; & D + E - 9)

End If
'Find Good Friday Easter Monday AscensionDay WhitMonday
If Indate = EasterDay - 2 Or Indate = EasterDay + 1 Or Indate = EasterDay + 39 Or Indate = EasterDay + 50 Then
WrkDay = False
Exit Function
End If
End Select
WrkDay = True 'If Indate is a workday
End Function

 

[OhioSteve]

Yeah, Jwigh's approach is basically an improved version of my &quot;METHOD TWO&quot;. I like it. You could start by subtracting one from the current date. Then, call Jwigh's function. If it returns false, subtract one again and repeat the process until it returns true.

However, it would still be very frustrating if the institution suddenly changed the rules for holidays. The crosswalk table would be much easier to modify.

 

[rdodge]

Okay, maybe I have too much time on my hands too, but here it goes. I have create a class module to give the exact dates of the various holidays. Instance on the class module is set to PublicNotCreatable, so as we can create a reference to it and not only that, but spreadsheets can also refer to them via the function calling.

Each function is declared as public for 2 reasons as well:

1 - Spreadsheets can refer to them
2 - Can use the CallByName function in a standard module in VBA. Note though, CallByName function can only refer to methods and properties that's in a class module, thus why the functions are in a class module.

Here's the Class Module side of it:

Public Function ThanksGiving(Year As Integer) As Date
ThanksGiving = DateSerial(Year, 11, 25) - Weekday(DateSerial(Year, 11, 25), 2) + 4
End Function
Public Function DayAfterThanksGiving(Year As Integer) As Date
DayAfterThanksGiving = DateSerial(Year, 11, 25) - Weekday(DateSerial(Year, 11, 25), 2) + 5
End Function
Public Function Christmas(Year As Integer) As Date
Christmas = DateSerial(Year, 12, 25)
End Function
Public Function ChristmasEve(Year As Integer) As Date
ChristmasEve = DateSerial(Year, 12, 24)
End Function
Public Function NewYear(Year As Integer) As Date
NewYear = DateSerial(Year, 1, 1)
End Function
Public Function NewYearEve(Year As Integer) As Date
NewYearEve = DateSerial(Year, 12, 31)
End Function
Public Function Independence(Year As Integer) As Date
Independence = DateSerial(Year, 7, 4)
End Function
Public Function Labor(Year As Integer) As Date
Labor = DateSerial(Year, 9, 7) - Weekday(DateSerial(Year, 9, 7), 2) + 1
End Function
Public Function Memorial(Year As Integer) As Date
Memorial = DateSerial(Year, 5, 31) - Weekday(DateSerial(Year, 5, 31), 2) + 1
End Function
Public Function GoodFriday(Year As Integer) As Date
GoodFriday = 6 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function HolyThursday(Year As Integer) As Date
HolyThursday = 5 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function HolyWednesday(Year As Integer) As Date
HolyWednesday = 4 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function HolyTuesday(Year As Integer) As Date
HolyTuesday = 3 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function HolyMonday(Year As Integer) As Date
HolyMonday = 2 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function PalmSunday(Year As Integer) As Date
PalmSunday = 1 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function Eastrday(Year As Integer) As Date
Dim Century As Integer
Dim YearFactor As Integer
YearFactor = 11 * ((Year Mod 19) + 1)
Century = Int(Year / 100)
Eastrday = DateValue(&quot;3/&quot; & 50 - (Int(Century / 4) + Int((8 * (Century + 11) / 25) - Century + YearFactor) Mod 30) & &quot;/&quot; & Year)
End Function
Public Function Easter(Year As Integer) As Date
Easter = 8 - Weekday(Eastrday(Year), 1) + Eastrday(Year)
End Function
Public Function AprilFools(Year As Integer) As Date
AprilFools = DateSerial(Year, 4, 1)
End Function
Public Function Veterans(Year As Integer) As Date
Veterans = DateSerial(Year, 11, 11)
End Function
Public Function MartinLutherKing(Year As Integer) As Date
MartinLutherKing = DateSerial(Year, 1, 21) - Weekday(DateSerial(Year, 1, 21), 2) + 1
End Function
Public Function President(Year As Integer) As Date
President = DateSerial(Year, 2, 21) - Weekday(DateSerial(Year, 2, 21), 2) + 1
End Function
Public Function Mother(Year As Integer) As Date
Mother = DateSerial(Year, 5, 8) - Weekday(DateSerial(Year, 5, 8), 2) + 7
End Function
Public Function Father(Year As Integer) As Date
Father = DateSerial(Year, 6, 15) - Weekday(DateSerial(Year, 6, 15), 2) + 7
End Function
Public Function Sweetest(Year As Integer) As Date
Sweetest = DateSerial(Year, 10, 16) - Weekday(DateSerial(Year, 10, 16), 2) + 6
End Function
Public Function Valentine(Year As Integer) As Date
Valentine = DateSerial(Year, 2, 14)
End Function
Public Function StPatrick(Year As Integer) As Date
StPatrick = DateSerial(Year, 3, 17)
End Function


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[jwigh]

Something is wrong in your calculation of easterdag. Easter can be in April. In my version I hav not translated the way yur handel dates. In Sweden we have a different order like 2003-11-24.

My function translated should be

If F <= 31 Then
EasterDay = CDate(&quot;03-&quot; & F & & &quot;-&quot; Year)

Else
EasterDay = CDate(&quot;04-&quot; & D + E - 9 & &quot;-&quot; & Year)

 

[rdodge]

As far as the Date order is concerned, I used the DateSerial function so as to not run into that issue. However, I just noticed that it doesn't return the correct date either. The spreadsheet formula side that I used, which by the way does return the correct result is:

=6-WEEKDAY(DATE(YEAR(TODAY()),3,50-MOD(INT(INT(YEAR(TODAY())/100)/4)+INT(8*(INT(YEAR(TODAY())/100)+11)/25)-INT(YEAR(TODAY())/100)+(11*(MOD(YEAR(TODAY()),19)+1)),30)),1)+DATE(YEAR(TODAY()),3,50-MOD(INT(INT(YEAR(TODAY())/100)/4)+INT(8*(INT(YEAR(TODAY())/100)+11)/25)-INT(YEAR(TODAY())/100)+(11*(MOD(YEAR(TODAY()),19)+1)),30))

Here's the revised EastrDay function, and I did test it out.

Public Function Eastrday(Year As Integer) As Date
Dim Century As Integer, LYCentury As Integer, Lunar As Integer
Dim YearFactor As Integer, PFM As Integer, CPFM As Date
YearFactor = 11 * ((Year Mod 19) + 1)
Century = Int(Year / 100)
LYCentury = Int(Century / 4)
Lunar = Int(8 * (Century + 11) / 25)
PFM = (LYCentury + Lunar - Century + YearFactor) Mod 30
CPFM = DateSerial(Year, 3, 50 - PFM)
Eastrday = 6 - Weekday(CPFM, 1) + CPFM
End Function


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[rdodge]

The above has also revised all of my other Easter related holidays as follows:

Public Function GoodFriday(Year As Integer) As Date
GoodFriday = Eastrday(Year) - 2
End Function
Public Function HolyThursday(Year As Integer) As Date
HolyThursday = Eastrday(Year) - 3
End Function
Public Function HolyWednesday(Year As Integer) As Date
HolyWednesday = Eastrday(Year) - 4
End Function
Public Function HolyTuesday(Year As Integer) As Date
HolyTuesday = Eastrday(Year) - 5
End Function
Public Function HolyMonday(Year As Integer) As Date
HolyMonday = Eastrday(Year) - 6
End Function
Public Function PalmSunday(Year As Integer) As Date
PalmSunday = Eastrday(Year) - 7
End Function
Public Function Easter(Year As Integer) As Date
Easter = Eastrday(Year)
End Function


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[OhioSteve]

rdodge, you are the man. When I mentioned that it was hypothetically possible to calculate the holidays without hardcoding, I did not expect that someone had actually attempted this task.

However, I think that you still have some missing pieces. Ultimately, we want a function where you can give any date, and it will return the previous workdate. You have given us the hard part, but not the easy part. We need to deal with border values. Like, we have to establish an upper and lower limit for the input date. Otherwise, someone could input a date from an archaic calender system.
Also, after you calc all of the holidays, I still think you should write them to an array. Then later in the function, it will be easy to check dates against the array.