Restarting Custom # Each Year Using Dmax function

G

gold007eye

Registered User.
Local time
Yesterday, 19:30
Joined
May 11, 2005
Messages
260
I have been racking my brain trying to get this to work with no success :(

I have read through these 2 posts numerous times trying to apply the coding/methodolgy and can't seem to get it fine tuned.

http://access-programmers.co.uk/forums/showthread.php?t=31046&p=519211
http://www.access-programmers.co.uk/forums/showthread.php?t=112804&highlight=customid

Here is the structure I need to use for the number:

AR-CR-2007-00001

What I need is for the 00001 section to reset to 0 each year so the numbers will look like such:

AR-CR-2007-00001
AR-CR-2007-00002
AR-CR-2007-00003

AR-CR-2008-00001
AR-CR-2008-00002
AR-CR-2008-00003 etc....

I have the code working great to generate the different sections.
I have a table with 3 fields (seperating the number) and 1 field that is the combined number. The fields are:

FormatID (Text field),YearID (Text field),BaseID (Number field ) (These are populated using a form called "Test")

FormatID = AR-CR- (This is set as the default format/value)
YearID = 2007- (I have this obtained automatically using code in the default
value of the data tab)
Code: 
=Right(Date(),4) & "-"

BaseID = 00001 (Sequential by +1)

I have a button on the form called "Save" which I have placed this code in the "On-Click" event.
Code: 
Private Sub Save_Click()
If IsNull(Me![BaseID]) Then
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]"), 0) + 1, "00000")
End If
Me![CustID] = [FormatID] & [DateID] & Format([BaseID], "00000")
End Sub

What I can't figure out is how to get the Base ID to restart with 00001 at the beginning of each year? What am I doing wrong?

I have also attached an image showing the increment change if I was to change the year to 2008 how it just incriments the last numbers instead of resetting to 00001.
 

Attachments

  • custom_db_number.jpg
    custom_db_number.jpg
    31.3 KB · Views: 693
you need to add a criteria to the dMax to filter for the year, something like

DMax("[BaseID]", "[tblTest]","[yearID]='& year(date) &"'")

HTh

Peter
 
No luck. I am getting "Compile Error: Expected: list seperator or )" it is referring to the ' in the code. Here is the new code I am using with your suggestion:

Code: 
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]","[YearID]='& year(date) & "'"), 0) + 1, "00000")
 
I'm not up on the new functions like YEAR.

Try DATEPART( "y", [date])

Also, there is the question of format.

IF the date in the table is text, then you need something silly like

"[YearID]='" & CStr$(DatePart( "y", [date] )) & '"

IF the date field is a number, you need a different type of comparison. I'm smelling a mixed-mode comparison here.
 
Ugghhh :( I have been trying different combos of the code.. still not working. Could someone please take a look at the same database I attached and see if they have any better luck? The increment works great just not when the year changes; I can't get it to reset the BaseID to 00001 for the new year.
 

Attachments

looks like I missed a quote mark

Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]", "[YearID]='" & Year(Date) & "'"), 0) + 1, "00000")

Though you should store BaseID as a number and format it to text when you use it. not checked but it may stop dmax working right so bear it in mind when testing.

Peter
 
Sweet! :) That helped.. I made a few modifications to the code and now it works like a charm. Thank for all the help!
 
Hi Can you tell me the changes you made to the code?
I would also like to use the same logic for my database.
If possible can you give the database file as you attached previously.....
Awaiting your reply......
Regards
Rahul
 
Rahul:

Here is the sample database. This actually has 4 parts that make up the final number. Hopefully this will help you.

Here is the code I used to compile the fields into 1 using the Save OnClick event.

Code: 
Private Sub Save_Click()
If IsNull(Me![BaseID]) Then
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]", "[YearID]='" & Year(Date) & "'"), 0) + 1)
End If
Me![CustID] = [FormatID] & [YearID] & "-" & Format([BaseID], "00000")
End Sub
 

Attachments

Last edited:
Hi, I have dowloaded the code to use in my database, and I must be missing something, because I can not get this to work.. I actually, need the number to reset based on year and permit code, I am attaching my database. Can anyone tell me what I did wrong. I know that I do not have the permit code correct in the dMax but I was just first trying to get it to autonumber. Thank in advance for any help

J.
 

Attachments

How might this code be changed to reset at the end of th fiscal year, Sept. 30th. instead of the calendar year'
Thanks,
Tedward

If IsNull(Me![BaseID]) Then
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]", "[YearID]='" & Year(Date) & "'"), 0) + 1)
End If
Me![CustID] = [FormatID] & [YearID] & "-" & Format([BaseID], "00000")
End Sub
 
You must log in or register to reply here.

Similar threads

D
Solved DMax
Replies
18
Views
730
Pat Hartman
P
S
Incremental Sequential Numbering by Year via Calculated Field
Replies
17
Views
757
CJ_London
C
F
Custom Sequential Number with Year-Numbers-One Letter
Replies
2
Views
481
Eugene-LS
Eugene-LS
A
auto number format on form
Replies
6
Views
643
gemma-the-husky
gemma-the-husky
T
How to create a custom random number AutoNumber with no repeats
Replies
11
Views
1,199
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom