X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Help With Fiscal Year And Custom Number., Access 2016    
post Oct 2 2017, 02:31 PM

Posts: 6
Joined: 18-September 17

Attached File  Copy.zip ( 1.43MB )Number of downloads: 8
Good afternoonall,
I have an issue that hopefully someone can help me with,
I have a button on a form that assigns a number based on fiscal year , YY, MM,custom number 0001-1000, so if I were to enter a new number to day it would be 18-10-0001 and so on, I'm trying to get this database to add new records using the new Fiscal year month and custom number 0001 and so on.
I have this code.
Private Sub btnAdd_Click()
    On Error GoTo LogErrors
    Dim MyDB As Database
    Dim myws As Workspace
    Dim rsnuest, rstesting, rsdummy As Recordset
    Dim stDocName, estnum1, tselfind As String, tselNuest, estnum As Integer
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set rsnuest = MyDB.OpenRecordset("EST", DB_OPEN_DYNASET)
    Set rstesting = MyDB.OpenRecordset("Testing", DB_OPEN_DYNASET)

     estnum1 = rsnuest!ESTNumber
If Month(Date) <= 9 Then
    Estdt = Right(Year(Date), 2) & "-0" & Month(Date) & "-"
    Estdt = Right(Year(Date), 2) & "-" & Month(Date) & "-"
End If
estnum = [estnum1] + 1

If estnum <= 9 Then
    estnew = "000" & estnum
ElseIf estnum > 9 And estnum < 100 Then
    estnew = "00" & estnum
ElseIf estnum >= 100 And estnum < 1000 Then
    estnew = "0" & estnum
ElseIf estnum >= 1000 Then
    estnew = estnum
End If

NuEst = Estdt & estnew

    msgcancel = "Press 'YES' to Add New Call Number & EST: "
    msgcancel = msgcancel & Me.NuEst
    msgcancel = msgcancel & ". Select 'NO' To Cancel" & Chr(13) & Chr(13)
    msgchoice = Dialog.Box(msgcancel, vbYesNo, "")

    Select Case msgchoice
        Case 6
            rsnuest![ESTNumber] = estnum

            rstesting![EST] = [NuEst]
            rstesting![ASSG_DATE] = Date
           ' rstesting![ASSIG_TO] = UCase([LastName])
            rstesting![STATUS] = "O"
           ' rstesting![Userid] = [Userid]

        stDocName = "frm_Testing"
        DoCmd.OpenForm stDocName, acNormal
        DoCmd.Close acForm, "frm_CallNumber", acSaveNo
    End Select
Exit Sub
Call LogErrors(err.Number, err.Description, "Form:frm_CallNumber, btnAdd_Click")
Resume Exit_btnAdd_Click
End Sub
Go to the top of the page
post Oct 2 2017, 02:34 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Does the same table include some sort of date field at all that would contain the month and year? If so, then you just need to store the four-digit number at the end. Then whenever you need to display this custom number in a query/form/report, you could use a simple function that concatenates the fiscal year of the given date, the month number of the given date and the four digit number.

EDIT: There are a couple of date fields, so I'm assuming that the table named "Testing" is a table of calls, and each call needs to have an "EST Number". Also, the Testing table is lacking a Primary Key to uniquely identify records. I'd recommend adding an autonumbered primary key field ("CallID") and changing the text "EST" field to "intESTNumber" with a datatype of Number-Integer. The code for adding the EST Number seems to do other stuff as well, so I'm not completely sure which parts to keep or change, but basically instead of assembling a string and assigning it to the call record, you can just look up the highest intESTNumber for the current fiscal year. Add 1 to that value, and you have the intESTNumber value for the new record. A function to display the full EST Number would be along these lines:

Public Function ESTNumber(lngCallID As Long) As String

    Dim dteCallDate As Date, intCallNumber As Integer
'   Get the two values from the record that determine the EST Number
    dteCallDate = DLookup("ASSG_DATE", "Testing", "CallID=" & lngCallID)
    intCallNumber = DLookup("intCallNumber", "Testing", "CallID=" & lngCallID)
'   First two digits - the fiscal year based on the ASSG_DATE value.
    If Month(dteCallDate) <= 9 Then
        ESTNumber = Year(dteCallDate) - 2000
        ESTNumber = Year(dteCallDate) - 2000 + 1
    End If
'   Next two digits - the month of the ASSG_DATE value.
    ESTNumber = ESTNumber & "-" & Format(Month(dteCallDate), "00")
'   Last four digits.
    ESTNumber = ESTNumber & "-" & Format(intCallNumber, "0000")

End Function

This way there's no need to deal with figuring out how many non-zero leading digits the last four digits has and such. Since users should never interact directly with the table data (only through forms, queries and reports), there's no need to worry about the data not containing the full string expression. Just calculate it on the fly whenever it's needed.

Hope this helps,

Go to the top of the page
post Oct 2 2017, 05:26 PM

Posts: 6
Joined: 18-September 17

Thanks for your assistance Doctor9.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th November 2018 - 12:38 AM