Help Assembling Serial Number

[mattc66 via AccessMonster.com]

I have been asked to create a tool to automate the process of creating a
serial number.

Example: 2007-H2C01 (This would be the first U type built in March 2007).

The number is created from the following:
Year:
Current Year

Product Type:
U: = 2
H: = 3

Month Code:
JAN = A
FEB = B
MAR = C
APR = D
MAY = E
JUN = F
JUL = G
AUG = H
SEP = J
OCT = K
NOV = L
DEC = M

Sequence Number:
Sequence number starting at 01 for the first one built in the month and
starting over each month.

So can anyone offer a suggestion on how I might do this?

Matt

 

[Michel Walsh]

With a table of each product, like:

Products ' table name
ProductID, DateOfCreation, Type, MarketingCode 'fields name



then for a new product, the MarketingCode would be:


Year(DateOfCreation) & "-" & Type & SWITCH(Type="U", 2, Type="H", 3, true,
0) &
CHR$(64+ Month(DateOfCreation)) &
(1+ DCOUNT("MarketingCode", "Products",
" Year(DateOfCreation) = " & Year(DateOfCreation) &
" AND Month(DateOfCreation) =" & Month(DateOfCreation) &
" AND DateOfCreation>=" & Format(DateOfCreation, "\#mm-dd-yyyy
hh:nn:ss\#") ))



where CHR$(64+ Month(DateOfCreation)) generates the month letter, and
Format(DateOfCreation, "\#mm-dd-yyyy hh:nn:ss\#") insures us that your date
will be formatted into the US style.



Hoping it may help,
Vanderghast, Access MVP

 

[mattc66 via AccessMonster.com]

How would I then assign a sequntial number starting with 01 for each unit
built that month and starting over each month with 01?

With a table of each product, like:

Products ' table name
ProductID, DateOfCreation, Type, MarketingCode 'fields name

then for a new product, the MarketingCode would be:

Year(DateOfCreation) & "-" & Type & SWITCH(Type="U", 2, Type="H", 3, true,
0) &
CHR$(64+ Month(DateOfCreation)) &
(1+ DCOUNT("MarketingCode", "Products",
" Year(DateOfCreation) = " & Year(DateOfCreation) &
" AND Month(DateOfCreation) =" & Month(DateOfCreation) &
" AND DateOfCreation>=" & Format(DateOfCreation, "\#mm-dd-yyyy
hh:nn:ss\#") ))

where CHR$(64+ Month(DateOfCreation)) generates the month letter, and
Format(DateOfCreation, "\#mm-dd-yyyy hh:nn:ss\#") insures us that your date
will be formatted into the US style.

Hoping it may help,
Vanderghast, Access MVP

I have been asked to create a tool to automate the process of creating a
serial number.

[quoted text clipped - 30 lines]

Matt

 

[mattc66 via AccessMonster.com]

If I create a sequence log is there a way that I could just grab the next
number not yet taken?

With a table of each product, like:

Products ' table name
ProductID, DateOfCreation, Type, MarketingCode 'fields name

then for a new product, the MarketingCode would be:

Year(DateOfCreation) & "-" & Type & SWITCH(Type="U", 2, Type="H", 3, true,
0) &
CHR$(64+ Month(DateOfCreation)) &
(1+ DCOUNT("MarketingCode", "Products",
" Year(DateOfCreation) = " & Year(DateOfCreation) &
" AND Month(DateOfCreation) =" & Month(DateOfCreation) &
" AND DateOfCreation>=" & Format(DateOfCreation, "\#mm-dd-yyyy
hh:nn:ss\#") ))

where CHR$(64+ Month(DateOfCreation)) generates the month letter, and
Format(DateOfCreation, "\#mm-dd-yyyy hh:nn:ss\#") insures us that your date
will be formatted into the US style.

Hoping it may help,
Vanderghast, Access MVP

I have been asked to create a tool to automate the process of creating a
serial number.

[quoted text clipped - 30 lines]

Matt

 

[Michel Walsh]

That is what the 1+ DCount( , , ) tries to do. Note that the criteria
count the number of records for the sameYear, and same month. You may have
to add the same type too ( just add something like

& " AND Type=""" & type & """"

assuming Type is a string value)

Hoping it may help,
Vanderghast, Access MVP

How would I then assign a sequntial number starting with 01 for each unit
built that month and starting over each month with 01?

With a table of each product, like:

Products ' table name
ProductID, DateOfCreation, Type, MarketingCode 'fields name

then for a new product, the MarketingCode would be:

Year(DateOfCreation) & "-" & Type & SWITCH(Type="U", 2, Type="H", 3, true,
0) &
CHR$(64+ Month(DateOfCreation)) &
(1+ DCOUNT("MarketingCode", "Products",
" Year(DateOfCreation) = " & Year(DateOfCreation) &
" AND Month(DateOfCreation) =" & Month(DateOfCreation) &
" AND DateOfCreation>=" & Format(DateOfCreation, "\#mm-dd-yyyy
hh:nn:ss\#") ))

where CHR$(64+ Month(DateOfCreation)) generates the month letter, and
Format(DateOfCreation, "\#mm-dd-yyyy hh:nn:ss\#") insures us that your
date
will be formatted into the US style.

Hoping it may help,
Vanderghast, Access MVP

I have been asked to create a tool to automate the process of creating a
serial number.

[quoted text clipped - 30 lines]

Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com