Special identifier

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
How do I concatenate?

Jeff Boyce said:
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>
Click to expand...
 
Alylia said:
How do I concatenate?
Click to expand...

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 said:
There are examples at mvps.org/access of what's termed "custom
autonumbers"
Click to expand...

Can you provide a link, please? The closest match I could find was:

http://mvps.org/access/tables/tbl0005.htm
Have Autonumber field start from a value different from 1

Could you please use your influence as an MVP to get them to add a link
to the following KB article, which tells how to specify the values for
seed (starting value) and increment:

http://support.microsoft.com/default.aspx?kbid=202121

Many thanks,

Jamie.

--
 
Sorry, don't have a link.

But a search at the site, or googling on "custom autonumber" should do it...
 
Jeff said:
Sorry, don't have a link.

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

No luck.

Sorry, I was ambiguous. I meant, please ask mvps.org to add a link to
the KB article 202121.

Thanks again,

Jamie.

--
 
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 said:
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.
Click to expand...

What's this got to do with Microsoft?

You are an MVP. I'm talking about a change to an article on mvp.org -
that's MVPs such as yourself, right? The mvp.org article describes a
workaround for seeding an autonumber. However, this can be achieved
*directly*, no workaround required. The KB article I linked to tells
how to do it.

I could ask mvp.org to change the page. However, while I had your
attention on the issue (I wouldn't just ask out of the blue), I asked
you to ask mvp.org because, as an MVP yourself, mvp.org may be more
responsive to you than me.

Jamie.

--
 
Jeff said:
You asked for a link to a KB -- parties involved include microsoft and
mvps...
Click to expand...

Yes and the Jet engine is a Microsoft product and the word Microsoft
appears in the name of this newsgroup.

Let's focus: as an MVP, do you have more influence than me over at
mvp.org? Forget the KB link, forget Microsoft: just tell them CREATE
TABLE Test (ID IDENTITY(1,1)).

Jamie.

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

Jeff
 
Back
Top