Date Problem (i.e., Fiscal Year)

[dnayana]

Thanks in advance to anyone who can assist me with the following problem (I hope I can explain this in the simplest way):

I am trying to generate a Tracking Number for all new records based on the Fiscal Year. The Fiscal Year is from Oct to Sep. The Tracking Number is formatted as follows: CY03-001, etc. Currently, there are existing records for FY03. I already have my code for the Fiscal Year, which is working properly. My problem comes in when I try to format a new record based on the new Fiscal Year. I have a test form that I am working with and if I enter 10/01/2003, then in my unbound text box, the value is CY05 when it should be CY04. I am not sure where the problem is coming from as I have tried stepping through my code. If anyone can take a look at the following code and inform me where I am making my mistakes at, I be forever grateful.

I have a form with three unbound text boxes.

TextBox1 (i.e., Me.FY) = 10/01/2003.
TextBox2 (i.e., Me.FYYear) displays 2004.
TextBox3 (i.e., Me.FYTrackNo) displays CY05; should display CY04.

Private Sub FY_LostFocus()
    Dim gFiscalYear1
    Dim nFiscalYear1
    Dim dtefY
    Dim iCount As Integer
    
    iCount = 1
    
    dtefY = Me.FY.Value
    
    ' Based on the given date, determine the fiscal year.
    If (Month(dtefY) >= 10) Then
        nFiscalYear1 = Year(dtefY) + 1
    Else
        nFiscalYear1 = Year(dtefY)
    End If
    Me.FYYear = nFiscalYear1
    Me.FYTrackNo = "CY" & Format(Me.FYYear.Value, "yy") & "-" & Format(iCount, "000")
End Sub

 

[boriska40]

hi,

instead Format(Me.FYYear.Value, "yy&quot
use Right(Me.FYYear.Value,2)

 

[Eupher]

boriska40 is right. Date/time calculations can be confusing, at least they are for me. Remember that the Year() function does not return a date value, but an integer corresponding to the year. Also, you haven't explicitly declared the data types of your variables, so Access is having to make some assumptions, and I believe it's setting nFiscalYear1 as a string, when actually it should be an integer. So your code is throwing Access a couple of curves and that's why you're getting unexpected results.

Ken S.

 

[dnayana]

Thank you sooooooooooooooo much boriska40 and Eupher!!!

Your help was so appreciated.