Anybody up for a challenge

  • Thread starter Thread starter JH
  • Start date Start date
J

JH

I'm not a programmer. I've done some nice databases without having to do
much coding. HOWEVER, I've been assigned to do a new, very small database.
One field has me stumped.

There are 5 fields:
Date
Vendor
Product
PO Number
Product

The tricky one is PO Number. What's needed is an autonumber based on the
date.

Example: 12050001, 12050002, etc.

The autonumber would use numbers based on the date (just mm/yy) and a 4
digit sequenced number after.

Trick: When the date changes to a new month/year the generated number will
automatically put the new mm/yy and start regenerating the sequenced numbers
(i.e. start over with 0001: i.e. 01060001, 01060002, etc.)

Really need help with this one.

James
 
Hi James,

First, I recommend changing the name of the Date field to something else,
because this is a reserved word. Personally, I would also eliminate the space
in the PO Number field, by renaming it to PONumber. Here are some references
for you on naming conventions and reserved words:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

What you are after is commonly referred to as an "Intelligent Key". You
might want to use Google Advanced Groups to search this term. The following
search results in 253 hits (With all of the words: intelligent key, Return
only messages from the group at this location: microsoft.public.access.*, and
Return messages posted between 1 January 2000 and 29 December 2005):

http://groups.google.com/groups?as_...0&as_maxd=29&as_maxm=12&as_maxy=2005&safe=off

You will likely want to store the mmyy and counter in separate fields and
then concatenate the results at run-time as needed. The counter can include a
Format statement to add the leading zeros. However, an autonumber is likely
not a suitable counter, since it can develop gaps. For example, if someone
starts to create a new record, but does not finish the process, an autonumber
will increment anyways. Thus, you could end up with records that had
PONumbers 12050001, 12050002, 12050005, 12050006, but no 12050003 or 12050004.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I'm not a programmer. I've done some nice databases without having to do
much coding. HOWEVER, I've been assigned to do a new, very small database.
One field has me stumped.

There are 5 fields:
Date
Vendor
Product
PO Number
Product

The tricky one is PO Number. What's needed is an autonumber based on the
date.

Example: 12050001, 12050002, etc.

The autonumber would use numbers based on the date (just mm/yy) and a 4
digit sequenced number after.

Trick: When the date changes to a new month/year the generated number will
automatically put the new mm/yy and start regenerating the sequenced numbers
(i.e. start over with 0001: i.e. 01060001, 01060002, etc.)

Really need help with this one.

James
 
The naming conventions you mentioned I am familiar with. I NEVER use spaces.
If it needs a "space" I use an underscore. The way it was written was just
for clarification.

There will be a date so I don't see the need to change it. The field in
question will not be named Date, it will be PONumber.

You've brought some points to view. Perhaps autonumber was the wrong term to
use. I know the limitations of autonumber and the gaps it leaves (especially
when the database gets cleaned - because of people not knowing how to follow
instructions). I'll look up intelligent key.

I figured the two numbers (mmyy and 000*) would have to be in separate
fields, I just don't know how to make mmyy automatically change when the
date field changes. The intelligent key search may turn up more.

Thanks for the extremely quick response.

JWH
 
Hi James,
...I just don't know how to make mmyy automatically change when the
date field changes.
Click to expand...
If the date field is based on the current system date, you should be able to
use the Format function with Date() in order to get what you want. For
example, from the immediate window:
?Format(Date(),"mmyy")
1205
The intelligent key search may turn up more.
Click to expand...
It should. This is a commonly asked question. Luckily, I've never had the
need to implement such a key in any of my databases.
Thanks for the extremely quick response.
Click to expand...
You're welcome. Happy New Year!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

The naming conventions you mentioned I am familiar with. I NEVER use spaces.
If it needs a "space" I use an underscore. The way it was written was just
for clarification.

There will be a date so I don't see the need to change it. The field in
question will not be named Date, it will be PONumber.

You've brought some points to view. Perhaps autonumber was the wrong term to
use. I know the limitations of autonumber and the gaps it leaves (especially
when the database gets cleaned - because of people not knowing how to follow
instructions). I'll look up intelligent key.

I figured the two numbers (mmyy and 000*) would have to be in separate
fields, I just don't know how to make mmyy automatically change when the
date field changes. The intelligent key search may turn up more.

Thanks for the extremely quick response.

JWH
 
Back
Top