generate number base on fiscal year and quarter 1

[tnguyen315]

I have a question similar to threat #927778.

http://www.tek-tips.com/viewthread.cfm?qid=927778

However, mine is generate number by quarter and fiscal year
Quarter 1: 10/01/2008 - 12/31/2008
Quarter 2: 01/01/2009 - 03/31/2009
Quarter 3: 04/01/2009 - 06/30/2009
Quarter 4: 07/01/2009 - 09/31/2009

And my number needs to generate like this:
If my first record enter date: 03/15/2009, then the key should be: 2009-Q2-0001, and so on 2009-Q2-0002, until the date enter reach to Q3, "04/01/2009" then the key would change to "2009-Q3-0001"

So please help, thanks.
I'm very appreciated.
Again, thanks for your valuable time.

 

[ProgramError]

Am I right in thinking you want to change the month to the quater and the the number indicates the record entered no matter what the day was when it was entered?



Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[TheAceMan1]

How are ya tnguyen315 . . .

In a module in the modules window, copy/paste the following function:

[blue]Public Function NextQtrKey(ByVal usrDate As Date) As String
   Dim Mth As String, Qtr As String, Build As String, Cri As String, DM As String
   
   Mth = Month(usrDate)
   
   If Mth <= 3 Then
      Qtr = "Q2"
   ElseIf Mth <= 6 Then
      Qtr = "Q3"
   ElseIf Mth <= 9 Then
      Qtr = "Q4"
   Else
      Qtr = "Q1"
   End If

   Build = Format(Year(usrDate), "####") & "-" & Qtr & "-"

   Cri = "left([ID],8) = '" & Build & "'"
   DM = CStr(Nz(DMax("clng(right([ID],4))", "tblDates", Cri)) + 1)
   DM = String(4 - Len(DM), "0") & DM
   
   NextQtrKey = Build & DM

End Function[/blue]

Just call the function [blue]with date supplied[/blue] to return the next index. Each year/quarter starts over at 1.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tnguyen315]

I'm so sorry for the late response. I just got back today.
Thanks very much The Aceman1 for your respond.
I run the Function and get the error at
DM = CStr(Nz(DMax("clng(right([ID],4))", "tblDates", Cri)) + 1)
with error message "Run time error '2001': You canceled the previous operation".
I don't know what to do, please help. Thanks

 

[tnguyen315]

The Aceman1, just ignored my post. I got it, thanks alots. The problem is I changed the "ID" in DM but not in Cri.
Again, thanks.
I'm very appreciated. You deserted a star...