Invoice number to be reset at the beginning of financial year (1 Viewer)

yogesh · Jul 2, 2012

Hello Friends,

I am preparing a form to create a new Invoice.

In this I want the invoice number field to be reset to 1 at the beginning of a new financial year.

Financial year eg. 2012 - 2013 (Begins on 1st April 2012 and ends on 31st March 2013)

Format for Invoice number 0001/2012-2013, 0002/2012-2013 and so on...

So how do i go about for creating a function in vba to achieve this.

Thanks in advance for your help.

Uncle Gizmo · Jul 2, 2012

Is the "Invoice number field" currently an auto increment field?

yogesh · Jul 3, 2012

No Sir. Its an integer.

Sketchin · Jul 13, 2012

I did this:

first calculated fiscal year and put in a text field in my invoice table:
=IIf(Month(Date())<4,Year(Date()),Year(Date())+1)

I have another field called [InvoiceNumber] in the same table that is calculated using this in a OnClick event for my "New INvoice" button:

If Me.InvoiceNumber = 0 Or IsNull(Me.InvoiceNumber) Then
Me.InvoiceNumber = Nz(DMax("[Invoicenumber]", "tblInvoice_Data"), 0) + 1
End If

Then on my form, I have a textbox with the control source:

=[FiscalYear] & "-GL" & Format([InvoiceNumber],"0000")

Hope this helps

moyna · Oct 29, 2012

I am also having the same problem. How to set Fiscal Year starting from 1st April to end at 31st March. Thanks for your help.

Sketchin · Oct 29, 2012

see the first part of my post above

moyna · Oct 30, 2012

Thanks for the advice. I am not able to set the Fiscal year to start with April 1st. Currently, I am working on a sample database (northwind) and saved this sample database with my desired file name. This file contains the start date set from Jan 1st, but I could manage to change the Monthly reports to show 1st quarter starting from April 1st to June 30. But when it comes to 4th Quarter, i.e. Jan 1st to March 31, how does it take.

I am not a VB or Access expert, but just trying to work based on the Northwind sample database.

Sketchin · Oct 30, 2012

Where/How are you using the fiscal year?

jai kushwaha · Nov 21, 2015

Sketchin said:
I did this:

first calculated fiscal year and put in a text field in my invoice table:
=IIf(Month(Date())<4,Year(Date()),Year(Date()) 1)

I have another field called [InvoiceNumber] in the same table that is calculated using this in a OnClick event for my "New INvoice" button:

If Me.InvoiceNumber = 0 Or IsNull(Me.InvoiceNumber) Then
Me.InvoiceNumber = Nz(DMax("[Invoicenumber]", "tblInvoice_Data"), 0) 1
End If

Then on my form, I have a textbox with the control source:

=[FiscalYear] & "-GL" & Format([InvoiceNumber],"0000")

Hope this helps

hello sir I am teri.g to make a database and I have a field id no and I want it in "yyyy/0000" this formate where first four are year and last four are increment no. and I want that on every 1st April the increment no reset from 0001. is it possible?? how to do it please reply as
fast as possible.. thank you..

gemma-the-husky · Nov 21, 2015

I would get the next number from a separate table. Then you can edit the number to suit.

I would not "structure" the number

given 0001/2012-2013, I would store the two bits separately
0001 AND 2012/2013

(fwiw, I wouldn't actually reset an invoice number to 1, or prefix numbers with a set amount of leading zeroes, to be honest, but that's a different issue, which is nothing to do with the database.)

arnelgp · Nov 21, 2015

the simplest solution is to create a new table, tblFYear with fields fyYear (Integer), fyStart (Short Date), fyEnd (Short Date). fill this table with correct year and corresponding dates of the fiscal year, ie:

fyYear = 2012
fyStart = 1/4/2012
fyEnd = 31/3/2013

fyYear = 2013
fyStart = 1/4/2013
fyEnd = 31/3/2014
...
etc. (up to 2050?)
...

then put this function in a module, and call it to get the unique id in "yyyy/0000" format:

Public Function fnGetUniqueID() As String

dim dt As Date
dim yr As Integer
dim strID As String
dim nPos As Integer
dt = Date()
yr = NZ(DlookUp("[fyYear]", "[tblFYear]", "#" & dt & "# Between [fyStart] And [fyEnd]"),0)
strID = DMax("yourIDField", "yourTableName", "Left(yourIDField, 4) = '" (yr & "") & "'"), yr & "/0000")
nPos = Instr(strID, "/")
fnGetUniqueID = Left(strID, nPos) & Format(Val(Mid(strID, nPos + 1)) + 1, "0000")
End Function

'**************
remember to substitute the correct field name and table name on the above function.

jai kushwaha · Nov 22, 2015

arnelgp said:
the simplest solution is to create a new table, tblFYear with fields fyYear (Integer), fyStart (Short Date), fyEnd (Short Date). fill this table with correct year and corresponding dates of the fiscal year, ie:

fyYear = 2012
fyStart = 1/4/2012
fyEnd = 31/3/2013

fyYear = 2013
fyStart = 1/4/2013
fyEnd = 31/3/2014
...
etc. (up to 2050?)
...

then put this function in a module, and call it to get the unique id in "yyyy/0000" format:

Public Function fnGetUniqueID() As String

dim dt As Date
dim yr As Integer
dim strID As String
dim nPos As Integer
dt = Date()
yr = NZ(DlookUp("[fyYear]", "[tblFYear]", "#" & dt & "# Between [fyStart] And [fyEnd]"),0)
strID = DMax("yourIDField", "yourTableName", "Left(yourIDField, 4) = '" (yr & "") & "'"), yr & "/0000")
nPos = Instr(strID, "/")
fnGetUniqueID = Left(strID, nPos) & Format(Val(Mid(strID, nPos + 1)) + 1, "0000")
End Function

'**************
remember to substitute the correct field name and table name on the above function.

is there any other way to do it???

can you tell me the process that how i can make the autonumber that if i delete any record the next record will take the deleted record number.
because in autonumber if i delete any record i cant enter the record with same number.

jai kushwaha · Nov 22, 2015

jai kushwaha said:
is there any other way to do it???

can you tell me the process that how i can make the autonumber that if i delete any record the next record will take the deleted record number.
because in autonumber if i delete any record i cant enter the record with same number.

i have the code reset every year yyyy/0001 with the event before insert
here are the code

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
Me![RIMYear] = Right(Year(Date), 2)
vLast = DMax("[RIMINC]", "[RoadIM]", "[RIMYear]=" & Right(Year(Date), 2))
If IsNull(vLast) Then
iNext = 1
Else
iNext = vLast + 1
End If
Me![RIMINC] = iNext
End Sub

arnelgp · Nov 22, 2015

if you want to reuse the invoice number after deleting them, instead of physically deleting the record just flag them as deleted by adding a boolean field (ie name IsDeleted) and set its value whenever you wish to delete it. so when new invoice is added the table is scanned first before creating new sequence.

another alternative is to create a new table that will hold the sequence no of the invoice that has been deleted. same thing you scan this table first before creating new sequence.

gemma-the-husky · Nov 22, 2015

autonumber.

you CANNOT expect an autonumber to maintain an intact sequence. For an intact sequence you either need

a) to get the next number from a separate table OR
b) use dmax to get the next number.

jai kushwaha · Nov 23, 2015

gemma-the-husky said:
autonumber.

you CANNOT expect an autonumber to maintain an intact sequence. For an intact sequence you either need

a) to get the next number from a separate table OR
b) use dmax to get the next number.

can you give me the code that where and how to use the maf to get the next number

gemma-the-husky · Nov 23, 2015

basically

highestnumber = dmax("invoicenumber","invoicetable")

so

nextnumber = highestnumber+1

Invoice number to be reset at the beginning of financial year (1 Viewer)

yogesh

New member

Uncle Gizmo

Nifty Access Guy

yogesh

New member

Sketchin

Registered User.

moyna

New member

Sketchin

Registered User.

moyna

New member

Sketchin

Registered User.

jai kushwaha

Registered User.

gemma-the-husky

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

jai kushwaha

Registered User.

jai kushwaha

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

gemma-the-husky

Super Moderator

jai kushwaha

Registered User.

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread