Reset sequential number

[Guest]

I have a sequential number that needs to be reset every week. The format of
the number is: YYWW9999, where YY is the year (e.g. 2005=05), WW is the week
number (from 1-52), and 9999 is a 4-digit sequential number. I basically
have 2 questions.
1) I'm using this formula to extract the WW from the current date.
weeknum = Format(Format(Date, "ww"), "00")
The problem is that if the first week at the beginning of the year
(Jan 1) is not a full week, then I might get WW=53 at the end of the year. I
can be clumsy and just re-assign WW=52 with the following:
If weeknum > 52 Then
weeknum = 52
End If
But is there a cleaner and better way than this?

2) which is more challenging is: How can I reset the sequential number every
week? For example, at the beginning of each week, let's say Monday morning
when we need a new number, we want the sequential number to reset to 0001.
Has anyone done this before? I thought it's a common practice in Excel.
Thanks in advance for any pointers.

 

[Guest]

You are in an Access news group, but I think your question is about Excel.
If not, post back and be more specific about where you want this number to
be. If it is about Excel, I recommend you try an Excel newsgroup.

 

[Guest]

Hi Klatuu,
my question is on MsAccess. I'm storing the sequential number in a field
called NextNumber in table SeqNumber. So, my whole sequential number (job) is
this:

num = ![NextNumber]
weeknum = Format(Format(Date, "ww"), "00")
If weeknum > 52 Then
weeknum = 52
End If
jobdate = Format(Date, "yy") & weeknum
strjob = jobdate & Format(num, "0000")
job = Val(strjob)

and then I increment and save NextNumber back in the table SeqNumber.

 

[Marshall Barton]

I have a sequential number that needs to be reset every week. The format of
the number is: YYWW9999, where YY is the year (e.g. 2005=05), WW is the week
number (from 1-52), and 9999 is a 4-digit sequential number. I basically
have 2 questions.
1) I'm using this formula to extract the WW from the current date.
weeknum = Format(Format(Date, "ww"), "00")
The problem is that if the first week at the beginning of the year
(Jan 1) is not a full week, then I might get WW=53 at the end of the year. I
can be clumsy and just re-assign WW=52 with the following:
If weeknum > 52 Then
weeknum = 52
End If
But is there a cleaner and better way than this?

2) which is more challenging is: How can I reset the sequential number every
week? For example, at the beginning of each week, let's say Monday morning
when we need a new number, we want the sequential number to reset to 0001.

Why not just save the year and week values in the next
number table so you can quickly tell if they've changed.

With CurrentDb.OpenRecordset("nextnumtable")
stryear = Format(Date, "yy")
strweek = Format(Format(Date, "ww"), "00")
If strweek > "52" Then strweek = "52"
If stryear & strweek = !YearNum & !WeekNum _
Then
num = ![NextNumber]
Else
num = 1
End If
strjob = jobdate & Format(num, "0000")
..Edit
!YearNum = stryear 'text field
!WeekNum = strweek 'text field
![NextNumber] = num +1 'long integer field
..Update
..Close
End With