Format Date Problem

[dnayana]

P.S. Since I was working within a form I originally posted this thread in the forms forum. But after thinking about it, I thought maybe I should've posted it here. And after posting for second time, I saw that I forgot to put in a subject ... People, please don't strangle me ...

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

 

[smozgur]

Use this:

Me.FYTrackNo = "CY" & Format(DateSerial(Me.FYYear.Value, 1, 1), "yy&quot & "-" & Format(iCount, "000&quot

Or this:

Me.FYTrackNo = "CY" & right(Me.FYYear.Value, 2) & "-" & Format(iCount, "000&quot

instead yours. It fails because you are trying to format a numeric value, not a date value.

Suat

Oz

http://www.TheOfficeExperts.com

 

[dnayana]

Thank you sooooooooooooooo much smozgur!!!

Your help was so appreciated.