I'm trying to use a value returned by a function I wrote as the control source in a report text box. I've verified that the function works and displayed the return calue with msgbox just before the end of the function. The value looks correct inside the function but the text box printed on the report displays zero. I've included the function (determines the number of weekdays in a month) as well as the control source definition for the text box. Any help / advice would be deeply appreciated.
control source definition:
=EeTime_WorkDaysInMonth(1,Year(Date()))
Function source code:
Public Function EeTime_WorkDaysInMonth(Mon As Integer, Yr As Integer) As Integer
Dim intFirstOfMonthWeekDay As Integer
Dim intDaysInMonth As Integer
Dim LeapYear As Boolean
On Error GoTo Err_EeTime_WorkDaysInMonth
intFirstOfMonthWeekDay = Weekday(DateSerial(Yr, Mon, 1))
LeapYear = IsDate("2/29/" & Yr)
EeTimeWorkDaysInMonth = 20
If Mon = 1 Then intDaysInMonth = 31
If Mon = 2 And LeapYear = True Then intDaysInMonth = 29
If Mon = 2 And LeapYear = False Then intDaysInMonth = 28
If Mon = 3 Then intDaysInMonth = 31
If Mon = 4 Then intDaysInMonth = 30
If Mon = 5 Then intDaysInMonth = 31
If Mon = 6 Then intDaysInMonth = 30
If Mon = 7 Then intDaysInMonth = 31
If Mon = 8 Then intDaysInMonth = 31
If Mon = 9 Then intDaysInMonth = 30
If Mon = 10 Then intDaysInMonth = 31
If Mon = 11 Then intDaysInMonth = 30
If Mon = 12 Then intDaysInMonth = 31
If intDaysInMonth = 29 Then
EeTimeWorkDaysInMonth = 21
If intFirstOfMonthWeekDay = vbSunday Then EeTimeWorkDaysInMonth = 20
If intFirstOfMonthWeekDay = vbSaturday Then EeTimeWorkDaysInMonth = 20
End If
If intDaysInMonth = 30 Then
EeTimeWorkDaysInMonth = 22
If intFirstOfMonthWeekDay = vbSunday Then EeTimeWorkDaysInMonth = 21
If intFirstOfMonthWeekDay = vbFriday Then EeTimeWorkDaysInMonth = 21
If intFirstOfMonthWeekDay = vbSaturday Then EeTimeWorkDaysInMonth = 20
End If
If intDaysInMonth = 31 Then
EeTimeWorkDaysInMonth = 23
If intFirstOfMonthWeekDay = vbSunday Then EeTimeWorkDaysInMonth = 22
If intFirstOfMonthWeekDay = vbThursday Then EeTimeWorkDaysInMonth = 22
If intFirstOfMonthWeekDay = vbFriday Then EeTimeWorkDaysInMonth = 21
If intFirstOfMonthWeekDay = vbSaturday Then EeTimeWorkDaysInMonth = 21
End If
GoTo GracefulExit_EeTime_WorkDaysInMonth
Err_EeTime_WorkDaysInMonth:
MsgBox "EeTime_WorkDaysInMonth ERROR: " & Err.Description
GracefulExit_EeTime_WorkDaysInMonth:
MsgBox "EeTime_WorkDaysInMonth Graceful Exit: " & EeTimeWorkDaysInMonth
End Function
Full Version: User defined function as control source in a report
Hi
Welcome to UtterAccess
Haven't looked through all of your code, but your function is named EeTime_WorkDaysInMonth and that's what you're using in the textbox control source, yet throughout the code you're refering to it as EeTimeWorkDaysInMonth (ie - without the underscore) which would be seen by the compiler as a totally different variable (or maybe it's just a typo in your post ? )
It's a good idea to have Option Explicit at the top of all of your code modules and compile the code. The compiler would have picked up on the error.
I'd start by fixing up the variable name first
Welcome to UtterAccess
Haven't looked through all of your code, but your function is named EeTime_WorkDaysInMonth and that's what you're using in the textbox control source, yet throughout the code you're refering to it as EeTimeWorkDaysInMonth (ie - without the underscore) which would be seen by the compiler as a totally different variable (or maybe it's just a typo in your post ? )
It's a good idea to have Option Explicit at the top of all of your code modules and compile the code. The compiler would have picked up on the error.
I'd start by fixing up the variable name first
yeah... i'm just going to go kick myself repeatedly now.
thanks very much.
thanks very much.
Hi
Don't kick too hard - we've all been there
Continued success with your project
Don't kick too hard - we've all been there
Continued success with your project
Consider using If ... Else If... Else...End If or preferably, the Select Case statement, instead of multiple If statements for efficiency. For example, if the month is 1, the first If statement already gives intDaysInMonth and then your code executes the next 12 If statements for nothing.
Similarly for the next set of If statements which is fairly inefficient.
An alternative algorithm is to inspect the 29th of the month (after taking care of Feb of non-leap year - Aside: What is the proper word for "non-leap"?) and work out the "additional days". Here is the code I tried which seems to give correct results:
Similarly for the next set of If statements which is fairly inefficient.
An alternative algorithm is to inspect the 29th of the month (after taking care of Feb of non-leap year - Aside: What is the proper word for "non-leap"?) and work out the "additional days". Here is the code I tried which seems to give correct results:
CODE
Public Function fnWeekdaysInMonth(intYear As Integer, _
intMonth As Integer) As Integer
Const MINWEEKDAYS As Integer = 20
Dim intDaysInMonth As Integer
Dim intTempResult As Integer
On Error GoTo fnWeekdaysInMonth_Err
Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
intDaysInMonth = 31
Case 2
If IsDate(intYear & "-02-29") Then
intDaysInMonth = 29
Else
intTempResult = MINWEEKDAYS
GoTo fnWeekdaysInMonth_Return
End If
Case Else
intDaysInMonth = 30
End Select
' Add appropriate weekdays for 29, 30, 31 of month if appropriate
Select Case Weekday(DateSerial(intYear, intMonth, 29))
Case vbMonday, vbTuesday, vbWednesday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 28)
Case vbThursday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 28) + _
(intDaysInMonth = 31)
Case vbFriday
intTempResult = MINWEEKDAYS + 1
Case vbSaturday
intTempResult = MINWEEKDAYS + Abs(intDaysInMonth = 31)
Case vbSunday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 29)
End Select
fnWeekdaysInMonth_Return:
fnWeekdaysInMonth = intTempResult
fnWeekdaysInMonth_Exit:
On Error Resume Next
Exit Function
fnWeekdaysInMonth_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
vbCrLf & "(Programmer's note: vbaDateTime.fnWeekdaysInMonth)", _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnWeekdaysInMonth_Exit
End Function
intMonth As Integer) As Integer
Const MINWEEKDAYS As Integer = 20
Dim intDaysInMonth As Integer
Dim intTempResult As Integer
On Error GoTo fnWeekdaysInMonth_Err
Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
intDaysInMonth = 31
Case 2
If IsDate(intYear & "-02-29") Then
intDaysInMonth = 29
Else
intTempResult = MINWEEKDAYS
GoTo fnWeekdaysInMonth_Return
End If
Case Else
intDaysInMonth = 30
End Select
' Add appropriate weekdays for 29, 30, 31 of month if appropriate
Select Case Weekday(DateSerial(intYear, intMonth, 29))
Case vbMonday, vbTuesday, vbWednesday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 28)
Case vbThursday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 28) + _
(intDaysInMonth = 31)
Case vbFriday
intTempResult = MINWEEKDAYS + 1
Case vbSaturday
intTempResult = MINWEEKDAYS + Abs(intDaysInMonth = 31)
Case vbSunday
intTempResult = MINWEEKDAYS + (intDaysInMonth - 29)
End Select
fnWeekdaysInMonth_Return:
fnWeekdaysInMonth = intTempResult
fnWeekdaysInMonth_Exit:
On Error Resume Next
Exit Function
fnWeekdaysInMonth_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
vbCrLf & "(Programmer's note: vbaDateTime.fnWeekdaysInMonth)", _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnWeekdaysInMonth_Exit
End Function
Thanks very much... that code was unnecessarily sloppy. i'm more comfortable syntactically in C than VB.
You're welcome... Glad we could help...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.