Problem with SQL Statement: AutoGenerating

[dnayana]

Thanks in advance to anyone who can be of assistance ...

I am trying to build on my existing code and still coming up with problems.

I have a field (i.e., trackingNo) which is formatted as follows: FY03 (the two digits after FY represents the fiscal year) and is auto-generated by the number of records (based on the trackingNo) for that FY (found in the table).

When the user clicks the "New" button, the function GenerateCY is ran. If there are existing records for that FY (i.e., FY03-038), then the next trackingNo will be incremented by 1 (i.e., FY03-039). If there are no records, then it is a start of a new FY (i.e., FY04), then the tracking no will start back at 1 and then increment by 1 for each new record in that FY. The new trackingNo will be FY04-001.

My thought process is to include a like clause in my sql statement to find the existing FYs (i.e., where TrackingNo), but I nothing that I write seems to work.

My GenerateFY code is as follows and generates when the user clicks on the new button:

Private Function GenerateFY()
    Dim sSql As String
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim iCount As Integer
    Dim sName As String
    Dim gFiscalYear As Integer 'Get Fiscal Year
    Dim cFiscalYear As Integer 'Get Calendar Year
    Dim dte As Date 'Current Date
    Dim iFY As Integer
    
    'Get Calendar Year
    cFiscalYear = Year(Date)
    
    'System's Date Determine's the Fiscal Year (when user clicks on btnNew)
    dte = Date
    
    If (Month(dte) >= 10) Then
        gFiscalYear = Year(dte) + 1
    Else
        gFiscalYear = Year(dte)
    End If
       
    'Format: FYxx
    iFY = Right(gFiscalYear, 2)
    
    sSql = "Select * tblConsultants" & _
    sSql = sSql & "WHERE trackingNo Like 'FY' & ify & '*';"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sSql)
    
    If rs.RecordCount = 0 Then  'New FY
        iCount = 1
        sName = "FY" & Right(gFiscalYear, 2) & "-" & Format(iCount, "000")
    Else                        'Existing FY
        iCount = rs.RecordCount + 1
        sName = "FY" & Right(gFiscalYear, 2) & "-" & Format(iCount, "000")
    End If
    Me.trackingNo = sName
    
    rs.Close
End Function

 

[TonyJollans]

Hi dnayana,

You don't say what results you're getting and what is wrong. I haven't checked through your code properly but you should be able to make it easier by using a DMax function:

Me.trackingNo = Format(Right(Nz(DMax("trackingNo","tblConsultants","trackingNo Like 'FY'" & ify & '*'"),"FYnn-000"),3),"000")

I haven't tried this so I might have made a mistake but just replacing everything after setting variable iFY with the above should do what you want.

Enjoy,
Tony

 

[dnayana]

Thanks Tony for replying.

My results are not finding the records in the table with the tracking no in the format of "CY03-" to therefore add a new tracking number based on the recordcount. When I add a new record it is always starting at "001" instead of the recordcount + 1.

Revised my code as follows:

Private Function GenerateCY3()
Dim dte As Date   'Get today's date
Dim gFiscalYear As Integer   'Extract CY
Dim strSQL As String  'Get # of records from table
Dim rst As DAO.Recordset
Dim iCount As Integer
Dim strLink As String
Dim strTrackNo As String

'Get Today's Date
dte = Date

'Extract FY
If (Month(dte) >= 10) Then
    gFiscalYear = Year(dte) + 1
Else
    gFiscalYear = Year(dte)
End If

strLink = "CY" & Right(gFiscalYear, 2) & "-*"

'Get # of records in table where Tracking No like gFiscalYear
strSQL = "Select * from tblTestIncrement where txtFormat like 'strlink';"
Set rst = CurrentDb.OpenRecordset(strSQL)

'Determine if TrackingNo already exist based on recordcount of strSQL

If rst.RecordCount = 0 Then 'No records found; start of new TrackingNo
    iCount = 1
    strTrackNo = "CY" & Right(gFiscalYear, 2) & "-" & Format(iCount, "000")
Else                        'Records found; existing TrackingNo
    iCount = rst.RecordCount + 1
    strTrackNo = "CY" & Right(gFiscalYear, 2) & "-" & Format(iCount, "000")
End If

'Set Tracking No text box
    Me.txtFormat = strTrackNo

'Close recordset
    rst.Close
End Function

 

[TonyJollans]

Hi dnayana,

I haven't time to test it at the moment but is the statement ..

strLink = "CY" & Right(gFiscalYear, 2) & "-*"

.. giving you a two-digit number with a leading zero??

Try forcing it with something like ..

Format(Right(gFiscalYear, 2),"00")

I should have time later this evening to do it properly and will check back in then.

Enjoy,
Tony

 

[dnayana]

There was a problem with my SQL statement ... I had ... like 'strlink' when I should've had ... like '" & strLink & "'

Thanks again Tony!