Access combining fields

[Guest]

I'm modifying a database I did not create that has been in use for several
years. There is an existing autonumber field (ProjectID). There is a text
field (TrackingNumber) where my internal user manually enters the year and a
three digit number indicating the order in which projects came in (2006-001,
2006-002, etc.). What we would like to do is have an auto-generated "tracking
number" for each year. To add a little twist to the puzzle, our fiscal year
starts in July, so starting Saturday (July 1) the year should be 2007. Every
fiscal year beginning, the administrator-type person archives the prior
year's database, cleans out the "old" projects and leaves the ongoing
projects in the database--essentially creating a new database for the fiscal
year. I'm relatively new at this so forgive me for potentially sounding like
a simpleton. Is it possible to add text to the beginning of the ProjectID for
all new entries? Meaning each new entry would look like 2007-0001, etc.

 

[Guest]

If you add it, it will prefix all the old records also. The Autonumber
should only be used to create a unique number - not necessarily without gaps.

If you want to do the work then backup the database first.

Add a text field named something like ProjectNum. Update the field with
[ProjectID]&â€-“&[TrackingNumber] to capture your current data.

In your data entry form use the ProjectNum field for your tracking number
text box. Add an unbound text box, not visible, and use it as default for
ProjectNum field.

Use the following in a query as record source for the unbound text box –

SELECT Max(PROJECT.ProjectNum) AS [Last Project Number],
Max(IIf(DatePart("yyyy",[ProjectNum])>=DatePart("yyyy",DateAdd("m",6,Date())),DatePart("yyyy",[ProjectNum])
& "-" & Right("0000" &
Val(Right([ProjectNum],4)+1),4),DatePart("yyyy",DateAdd("m",6,Date())) &
"-0001")) AS [Next Project Number]
FROM PROJECT;

 

[Guest]

Yikes! My head is spinning. How about something a little simpler, like
creating a new text field with some sort of argument in an event that looks
for the largest entry and adds 1 for the next entry? So I'd have a constant
of 2007-___. The underscore part would be a three digit number. The argument
searches for the highest number (iMax ?) then adds one to it.