Hi,
Sorry to say, This problem has re-occurred, I seem to have a problem with one line in my code!
I am calling a function called “GetCalendarWeek” (located in the Script Library) and I want to put the returned value in a field (Text Field) on the form - I have done all the suggestions from the previous post, but its still happening…here is the code and the offending function :
Sub Initialize
Dim session As New NotesSession
Dim database As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim db As NotesDatabase
Dim kpidoc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("KPIDailyStatsVW")
Call view.Refresh
Msgbox "Here"
Set kpidoc = db.CreateDocument
Msgbox "Create Document - OK"
kpidoc.Form = "KPIWeeklyStatsFRM"
Msgbox "Set Form - OK"
--------- ******'Problem with the line below - Type Mismatch when sheduled **********---------------
kpidoc.YearWeekTX = GetCalendarWeek
Msgbox "All OK"
'Initialising Variables
dayofweek = Weekday(Now)
std = 0 'Standard Input Actuals
eng = 0 'Engineered Input Actuals
erimo = 0
erind = 0
ermar = 0
eroil = 0
eroilp = 0
erpwr = 0
erwtr = 0
ereng = 0
erstd = 0
ertot = 0
qsimo = 0
qsind = 0
qsmar = 0
qsoil = 0
qsoilp = 0
qspwr = 0
qswtr = 0
qseng = 0
qsstd = 0
qstot = 0
bdeng = 0
bdstd = 0
bdtot = 0
engot = 0
englt = 0
engtot = 0
engotval = 0
engltval = 0
engtotval = 0
engop = 0
stdot = 0
stdlt = 0
stdtot = 0
stdotval = 0
stdltval = 0
stdtotval = 0
stdop = 0
If dayofweek < 7 Then
kpidate = Now - dayofweek
query$ = "FIELD DateDT >= " & Format$(kpidate, "dd/mm/yyyy") & " And FIELD DateDT <= " & Format$(Now, "dd/mm/yyyy")
j = view.FTSearch( query$, maxDocs% )
Set doc = view.GetFirstDocument
Do While Not (doc Is Nothing)
Call CalculateTotals(doc)
Set doc = view.GetNextDocument(doc)
Loop
Call SetFormTotals(kpidoc, Cint(j))
Call view.clear
Else
query$ = "FIELD DateDT = " & Format$(Now, "dd/mm/yyyy")
j = view.FTSearch( query$, maxDocs% )
Set doc = view.GetFirstDocument
Do While Not (doc Is Nothing)
Call CalculateTotals(doc)
Set doc = view.GetNextDocument(doc)
Loop
Call view.clear
Call SetFormTotals(kpidoc, Cint(j))
End If
End Sub
The offending function:
Function GetCalendarWeek
%REM
This function calculates the calendar week number
(ISO standard) for a given date value. The format
function of LotusScript (parameter “ww”) does not solve
this problem.
Monday is the first day of the week. Week #1 is the week
that contains the 4th of January (ISO 8601). The week at the
end/beginning of the year belongs to the next/previous year,
if there are 3 days or less of that week in the year in question.
Christian Meis, 4.2.2000
%END REM
Dim InputDateOffset As Integer
Dim YearInQuestion As Integer
Dim January4 As Variant
Dim January4Offset As Integer
Dim FirstMondayOfYear As Variant
Dim January1Offset As Integer
Dim December31Offset As Integer
Dim weeknum As Integer
Dim inputdate As Variant
inputdate = Now
'Add 2 to inputdate as Weir Week Starts on a Saturday not Monday like ISO Week
inputdate = inputdate + 2
’ The year value is preset with that of the input date
YearInQuestion = Year( inputdate )
’ Calculate offset to monday from the input date
InputDateOffset = CalculateIsoWeekday( inputdate )
’ Calculate offsets for the first/last day of the year
January1Offset = CalculateIsoWeekday( Cdat( "01/01/" & Cstr( YearInQuestion ) ) )
December31Offset = CalculateIsoWeekday( Cdat( "31/12/" & Cstr( YearInQuestion ) ) )
’ If the input date is before the 4th of January and the year starts with
’ a friday, saturday or sunday, the week belongs to the previous year
’ if the entered date is not a monday or tuesday
If Month( inputdate ) = 1 And Day( inputdate ) < 4 And January1Offset> 3 And InputDateOffset > 1 Then
YearInQuestion = YearInQuestion - 1
End If
’ If the input date is after the 28th of December and the year ends with
’ a monday, tuesday or wednesday, then the week belongs to the following year
’ if the entered date is not a saturday or sunday
If Month( inputdate ) = 12 And Day( inputdate ) > 28 And December31Offset < 3 And InputDateOffset < 5 Then
YearInQuestion = YearInQuestion + 1
End If
’ The 4th of January defines week #1
January4 = Cdat( "04/01/" & Cstr( YearInQuestion ) )
’ Offset to the monday of week #1
FirstMondayOfYear = Cdat( January4 - CalculateIsoWeekday( January4 ) )
’ The time range between the monday of week #1 and the monday
’ of the week in question is divided by 7, plus 1 for the first week
weeknum = ( inputdate - InputDateOffset - FirstMondayOfYear ) \ 7 + 1
’ The return value is a string with the week number and the year
If Len(Cstr( weeknum )) = 1 Then
GetCalendarWeek = Cstr( YearInQuestion ) & "W0" & Cstr( weeknum )
Else
GetCalendarWeek = Cstr( YearInQuestion ) & "W" & Cstr( weeknum )
End If
End Function
Does anyone have any suggestions as to why this isn’t working…I have been working on this for weeks and I have no hair left to Pull!!!
Thanks
F1H