Need some help

  • Thread starter Thread starter DavidPuddyFan
  • Start date Start date
D

DavidPuddyFan

Have a small project in front of me, and I am just trying to figure out
how to do it...I think I have an idea, but I wanted to get some other
ideas as well...basically the main thing I need to to is automate a
serial number, which is no big deal, within the db whenever a new entry
is added...where I need the help is here...I need to the serial number
to be in this format:
V044110001

Where 04 is the year, 41 is the week number, 10 is the day, and 001 is
the incremental number for the day......so for instance, if I were to
enter 2 entries into the db on Jan 2, then the serial numbers would be
V040102001 and V040102002 respectively....then for Jan 3rd, they would
be V040103001 and V040103002....

Now I just have to find a way to do it....thanks
 
here you go (note I limited day to 1..7 - no need for day of month ) - btw
what happens in 2100??? y2k all over(!)

Function GenerateSerial(Optional ByVal TheDate As Date) As String
Dim Rs As DAO.Recordset
Dim Ret As String
If TheDate = 0 Then TheDate = Date
Ret = "V" & Format(DatePart("yy", TheDate), "00") & _
Format(DatePart("ww", TheDate, vbUseSystemDayOfWeek, vbUseSystem),
"00") & _
Format(DatePart("w", TheDate, vbUseSystemDayOfWeek, vbUseSystem),
"00")
Set Rs = Access.CurrentDb.OpenRecordset("SELECT MAX(DATESERIAL) FROM
TABLE1 WHERE DATESERIAL LIKE '" & Ret & "*'", dbOpenSnapshot)
If IsNull(Rs.Fields(0).Value) Then
GenerateSerial = Ret & "001"
Else
GenerateSerial = Ret & Format(Val(Mid(Rs.Fields(0).Value, Len(Ret) + 1))
+ 1, "000")
End If
Rs.Close: Set Rs = Nothing
End Function

hth

--
Pieter Wijnen

When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca
http://allenbrowne.com/
 
DavidPuddyFan said:
Have a small project in front of me, and I am just trying to figure out
how to do it...I think I have an idea, but I wanted to get some other
ideas as well...basically the main thing I need to to is automate a
serial number, which is no big deal, within the db whenever a new entry
is added...where I need the help is here...I need to the serial number
to be in this format:
V044110001

Where 04 is the year, 41 is the week number, 10 is the day, and 001 is
the incremental number for the day......so for instance, if I were to
enter 2 entries into the db on Jan 2, then the serial numbers would be
V040102001 and V040102002 respectively....then for Jan 3rd, they would
be V040103001 and V040103002....

Now I just have to find a way to do it....thanks
Click to expand...
First, get the prefix for your number:
prefix = "V" & format$(date(),"yy") & weekNumber(date()) & weekday(Date())

I leave the weekNumber function to you. Also note weekday will need
formatting to two digits.

Next, get the highest corresponding suffix number from your table.
One way to do this would be to open a recordset containing only the serial
numbers
for the current day in descending order - the first record would be the
highest serial
number for the current day or null if there were no records yet for the
current day.

Finally, you have to extract the serial number from the last three digits,
increment it,
and format your new serial number.

HTH

David
 
Back
Top