View Single Post
Old April 4th, 2008, 07:24 AM
SerranoG SerranoG is offline
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

Hmmm... if the "AAAA" part is always that and the only thing that changes are the year and number, consider this. How about spliting the ID field into just two fields: the year (say, intYear; better to have four digits to be Y2K compliant, remember that?), and a long integer (say, lngID). Then for forms and reports you simply DISPLAY the full ID using concatenation of "AAAA" & "-" & intYear & "-" & Format(lngID, "00000000").

To add one to the ID would be simply

lngID = Nz(DMax("[lngID]" , "TMEMBER", "[intYear] = " & Year(Date())), 0) + 1

That is, if the year is automatically calculated from the current year. If there are no members for the year 2009 then the Nz() function would return zero and the count would start at 1.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division