Generate Unique ID Combination for new record

[delontan]

Hi,

I am doing a web database using asp page. i am using MS Access to create table. I have problem of dealing with the order id. My order id is a combination of 1st letter of the month, followed by year (2 digits), then 3rd letter of current month, then followed by a suffix of 4 digits (from 0000-9999)

for i.e. April 2004,

A04R0001
A04R0002 and so on.....until a new month
and,

for every new month, the suffix will be reset to 0000 and when new record is added the suffix should become 0001. prefix remain the same format as mentioned above.

for i.e. M04Y0001
M04Y0002 and so on...

i have tried to used default values in the table properties but it won't work for the suffix.
(this is what i did)
=Mid(MonthName(Month(Date()),True),1,1) & Format(Now(), "yy") & UCase(Mid(MonthName(Month(Date()),True),3,1))

any idea how i can go about doing this so whenever i submit a new order form from my web page, the order id will be automatically generated and inserted into the new record with other order information such as customer name and etc.

i have tried access database and relationshop forum but it doens't help me much.

Plz help!!!!

 

[simonchristieis]

if you are just after a way of creating a unique field for each record then:

DateDiff("s", Now(), cdate("1/1/3000 00:00:00"))

will give you the number of seconds til the next millenia

alternatively, reversing the date allows you to calculate which came first:

yrmonthday
20040427 ' today
20040426 ' yesterday
20030523 ' my birthday last year
19861225 ' christmas day 1986 (great presents !!)

hth

simon

 

[delontan]

no, i am after a way of creating a custom combination for the order id which i mentioned earlier.
the format is a prefix with 1st letter of the current month + 2 digits of currrent year + 3rd letter of the current month and the suffix is a 4 digits number that will go from 0001 to 9999.

for example, if the first user submit the form from a web page now, (date is April 27, 2004), the value A04R0001 will be generated and updated somehow to the specific MS Access database table order id field.
when second user submit a new order form again, now, the value A04R0002 will be submitted and so on. Until a change of a month, for i.e a new month, let say May 1, 2004. when user submit the order form, the order id will be M04Y0001, reset back to 0001 for the first user of the new month.

i am using ASP and MS Access. so i am thinking either i generated the custome value from Access itself or using ASP codes to generate it during the process of submission.
Maybe there are other ways to do it.

plz help... thanks!

 

[ChrisHirst]

your idea is at risk of generating the same ID number for multiple orders. In between getting the next sequential ID from the db, generating the code and then updating the db, another session could have easily retrieved the same next ID number.
You could of course have a table that just held the next number, but there, if you get the next ID then update the table you have the problem of an order being abandoned before submission and you lose a sequence.

A better way is to allow the db to create the ID in a autonumber field then return this back to the browser with your month and year code concantenated to it as the customers reference number, this will ensure that the order id is unique.



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[delontan]

hi chris, thanks for you info...i thought of that using 2 fields (one for the suffix(autonumber) and one for the prefix). but my problem is for every new month, the autonumber field has to reset to 0001 again, u know? and u can't alter autonumber field, right? any more idea how can go about doing that?

 

[lebisol]

make it just a number vs. autoincrement and then run your own "increment" function:
....maybe u could consider 3 fields
Month| Year | serial
=====================
Jan | 04 | 007
-find the last 'highest' record then incement it
varNextID= (last CurrentID) +1
then insert this varNextID....
varNextID= 007+1


ItemID=Month & Year & varNextID

but the problem still stands at concurrnet connections where both users get to "increment the same ID". Perhpas u should do a "find/SELECT" for newly created ID before the actual INSERT of happens...that way u would know if someone has created it in the time it took u to "compleate the web form" and submit it


"but my problem is for every new month, the autonumber field has to reset to 0001 again, u know"

- then most definetly u can not use auto-number;
there is only 12 months...so u can estimate/store them and then later comapre the current date perhaps using a condition like


IF Today <= 1/1/99 then use JAN
IF Today <= 2/2/...etc.

maybe add a small table that will hold a relationship of
1 year to many months .....depens if u like to 'script' or 'store/pull/compare' against your DB....
good luck!

> need more info?
:: don't click HERE ::