Cant calculate fiscal month 1

[vba317]

I am using access 2003. I am trying to write a function that will populate 12 cells on a spreadsheet with the fiscal month on them. The problem I am having is as soon as my calculation gets to 0 I get an error, this makes sense to me on why I get an error but I dont know what I can do differently to overcome this problem. Any help is greatly appreciated.

Tom

Option Compare Database
Option Explicit

Public Function CurRptMon(pd As Integer, strCurRptMon As String, iCurMon As Integer, iCurYr As Integer) As Integer
Dim dtCurDate As Date
Dim dtCurMon As Date
Dim dtMonthName As Date
Dim iCurPd As Integer
Dim iCurRptMon As Integer
Dim iCurMonPd As Integer
Dim ipdCalc As Integer
Dim ipdMonCalc As Integer
Dim strCurMon As String

dtCurDate = Date
iCurYr = Year(dtCurDate)
iCurMon = Month(dtCurDate)
iCurRptMon = iCurMon - 1
strCurRptMon = MonthName(iCurRptMon, False)
strCurMon = MonthName(iCurMon, True)
ipdCalc = 359
ipdMonCalc = 12
If iCurYr = 2013 Then ipdCalc = ipdCalc + ipdMonCalc
If iCurYr = 2014 Then ipdCalc = ipdCalc + (ipdMonCalc * 2)
If iCurYr = 2015 Then ipdCalc = ipdCalc + (ipdMonCalc * 3)
If iCurYr = 2016 Then ipdCalc = ipdCalc + (ipdMonCalc * 4)

pd = ipdCalc + iCurMon

End Function

Call CurRptMon(pd, strCurRptMon, iCurMon, iCurYr)

Option Compare Database
Option Explicit
Public Function SetTitle()

Dim strTitle1Name As String
Dim strTitle2Name As String
Dim strColTitle1Name As String
Dim strColTitle2Name As String
Dim strColTitle3Name As String
Dim strColTitle4Name As String
Dim strColTitle5Name As String
Dim strColTitle6Name As String
Dim strColTitle7Name As String
Dim strColTitle8Name As String
Dim strColTitle9Name As String
Dim strColTitle10Name As String
Dim strColTitle11Name As String
Dim strColTitle12Name As String

strTitle1Name = "PROCEDURES BY SERVICE PROVIDER - FISCAL " & iCurYr
strTitle2Name = "HOSPITALIST PROGRAM"
With goXL.ActiveSheet
.Cells(1, 1).Value = strTitle1Name
.Cells(2, 1).Value = strTitle2Name

strColTitle1Name = "UCI"
strColTitle2Name = "SERVICE PROVIDER"
strColTitle3Name = MonthName(iCurMon - 12, True)
strColTitle4Name = MonthName(iCurMon - 11, True)
strColTitle5Name = MonthName(iCurMon - 10, True)
strColTitle6Name = MonthName(iCurMon - 9, True)
strColTitle7Name = MonthName(iCurMon - 8, True)
strColTitle8Name = MonthName(iCurMon - 7, True)
strColTitle9Name = MonthName(iCurMon - 6, True)
strColTitle10Name = MonthName(iCurMon - 5, True)
strColTitle11Name = MonthName(iCurMon - 4, True)
strColTitle12Name = MonthName(iCurMon - 3, True)
strColTitle13Name = MonthName(iCurMon - 2, True)
strColTitle14Name = MonthName(iCurMon - 1, True)

iCol = 4

'.Cells(iCol, 1).Value = strColTitle1Name
'.Cells(iCol, 2).Value = strColTitle2Name
'.Cells(iCol, 3).Value = strColTitle3Name
'.Cells(iCol, 4).Value = strColTitle4Name
'.Cells(iCol, 5).Value = strColTitle5Name
'.Cells(iCol, 6).Value = strColTitle6Name
'.Cells(iCol, 7).Value = strColTitle7Name
'.Cells(iCol, 8).Value = strColTitle8Name
'.Cells(iCol, 9).Value = strColTitle9Name
'.Cells(iCol, 10).Value = strColTitle10Name
'.Cells(iCol, 11).Value = strColTitle11Name
.Cells(iCol, 12).Value = strColTitle12Name
.Cells(iCol, 13).Value = strColTitle13Name
.Cells(iCol, 14).Value = strColTitle14Name


End With

End function

 

[dhookom]

You stated "my calculation gets to 0 I get an error". Where does this happen and what do you expect to do with 0 values? Do you want to convert the 0 to 12 or something?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I get an error
Which error on which line ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, replace this:
iCurRptMon = iCurMon - 1
with this:
iCurRptMon = Month(DateAdd("m", -1, dtCurDate))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I get the error on the following lines.

strColTitle3Name = MonthName(iCurMon - 12, True)
strColTitle4Name = MonthName(iCurMon - 11, True)

Since the Current Month (iCurMon is 11) I am assuming I am getting the error because 11-11=0 and 11-12 is -1.
I tried PHV and I get the error sooner because it sets the CurMon to -1
Tom

 

[vba317]

I get runtime error 5 invalis call or procedure on the following line.

strColTitle3Name = MonthName(iCurMon - 12, True)

 

[PHV]

Replace all iCurMon - X with Month(DateAdd("m", -X, dtCurDate))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV, I appreciate your suggestion. Your code solves the error problem but now I am getting 10 instead of Oct.
Tom

 

[PHV]

Why did you get rid of the MonthName call ?
strColTitle3Name = MonthName(Month(DateAdd("m", -12, dtCurDate)), True)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

A little cumbersome, but how about...

If strColTitle3Name = MonthName(iCurMon - 12, True) < 1 Then
    strColTitle3Name = MonthName(iCurMon, True)
Else
    strColTitle3Name = MonthName(iCurMon - 12, True)
End If
 
If strColTitle4Name = MonthName(iCurMon - 11, True) < 1 Then
    strColTitle4Name = MonthName(iCurMon - 1, True)
Else
    strColTitle4Name = MonthName(iCurMon - 11, True)
End If

Randy

 

[vba317]

Thanks PHV your help is appreciated.

Tom