Special identifier

[Alylia]

I have taken up the task of building a access databse from an existing paper
based database for my new office. This piece of work seems to be simple
except that the access databse also has to continue using the "designer
identifier" that is being used in the manual system. I have explored all
possibilities but without any success. The identifier should look like this:
...-..-... The first two dots represents the last two digits of the current
financial period (which run from 1July - 30 June), followed by a dash, then
the next two dots represents BB which is also followed by a dash. The last
three dots increases as new records are being added.
Examples of this identifier for this current FY would be 05-BB-001;
05-BB-002 and on and on as new records are being added. This would be very
similar to an autonumber datatype. However, the identifier should be
sufficeincy flexible to change to 06-BB-... when the new FY starts on the 1
July 2005

Can someone please help urgently

Thx

 

[Jeff Boyce]

Alylia

You are describing three separate "facts", so plan on using three separate
fields, and concatenating the values together in queries, reports and on
forms. Actually, if the second position is always (ALWAYS) going to be
"BB", you don't need to waste a field for that.

And if you are already storing a date in each record, you won't need to
store your YY value -- build a simple function that returns YY, given date.

There are examples at mvps.org/access of what's termed "custom
autonumbers" -- they'll give you ideas about how you can create a sequence
number. The general approach is to look in the data for the largest
sequence number so far, then add one. And you can use this approach to
"reset" at the start of a new fiscal period.

 

[Alylia]

How do I concatenate?

Alylia

You are describing three separate "facts", so plan on using three separate
fields, and concatenating the values together in queries, reports and on
forms. Actually, if the second position is always (ALWAYS) going to be
"BB", you don't need to waste a field for that.

And if you are already storing a date in each record, you won't need to
store your YY value -- build a simple function that returns YY, given date.

There are examples at mvps.org/access of what's termed "custom
autonumbers" -- they'll give you ideas about how you can create a sequence
number. The general approach is to look in the data for the largest
sequence number so far, then add one. And you can use this approach to
"reset" at the start of a new fiscal period.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Rick Brandt]

How do I concatenate?

Given a query on a table having the fields...

FirstName
LastName

....these are concatenated to display the entire name in a single output field
with...

FullName: [FirstName] & " " & [LastName]

 

[Jeff Boyce]

Sorry, don't have a link.

But a search at the site, or googling on "custom autonumber" should do it...

 

[Jeff Boyce]

Jamie

MVPs are just folks. We don't work for Microsoft. We're volunteering our
time in the 'groups. You can post your suggestion directly to Microsoft,
and can leave a message at the mvps.org/access site.

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

You asked for a link to a KB -- parties involved include microsoft and
mvps...

 

[Jeff Boyce]

Sorry, my bad. That was my (obscured) point. As an MVP, I DON'T have any
more influence over them.

Jeff