By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

Reset Increment number on new month

P: n/a
Kd
I have the OrderNo set as the following
Right(Format(Date(),"yyyy"),1) & Format(Date(),"mm") &
"-"Format([OrderNo],"000")+1
This works great until
I get to a new month and need the numbers to reset like the following
example
512-001
Anybody have any thoughts to point me in the right direction,got stuck
with making updates to someone elses created database and getting
frustrated.

Any thoughts or ideas would be appreciated
Thank you

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 21 Nov 2005 20:40:57 -0800, "Kd" <ke******@yahoo.com> wrote:
I have the OrderNo set as the following
Right(Format(Date(),"yyyy"),1) & Format(Date(),"mm") &
"-"Format([OrderNo],"000")+1
This works great until
I get to a new month and need the numbers to reset like the following
example
512-001
Anybody have any thoughts to point me in the right direction,got stuck
with making updates to someone elses created database and getting
frustrated.

Any thoughts or ideas would be appreciated
Thank you


The example you gave must be from memory because that won't quite work, but in
any case, I think the most straightforward approach is to simply have a table
with month and year as fields in the key, and a NextSuffix field that gets
incremented for each new record.

I would not use existing records to determine the last number in use because a
deleted record could have its number reused, and that's bad for any kind of
auditing or reconciliation you have to do. I would also not just keep a
single counter and reset it because you may at some point need to insert a
record with a prior effective date.

The function to get a suffix value for a new record could be something like
the following (untested)...

Public Function NewSuffixInMonth( _
SuffixTable As String, _
RecDate As Date _
) As String
Dim lngResult As Long

On Error Goto Err_Catch

Dim dbs As DAO.Database
Set dbs = CurrentDB()

Dim qdf As DAO.Querydef
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "SELECT * FROM [" & SuffixTable & "] " & _
"WHERE [" & SuffixTable & "].YearNum = prm_YearNum AND " & _
"[" & SuffixTable & "].MonthNum = prm_MonthNum

Debug.Assert qdf.Parameters.Count = 2

qdf.Parameters!prm_YearNum = DatePart("yyyy", RecDate)
qdf.Parameters!prm_MonthNum = DatePart("m", RecDate)

Dim rstSuffix As DAO.Recordset
Set rstSuffix = qdf.OpenRecordset(dbOpenDynaset)

If rst.RecordCount > 0 Then
rst.Edit
lngResult = rstSuffix!SuffixNum + 1
Else
rst.AddNew
lngResult = 1
End If

rst!SuffixNum = lngResult
rst.Update

Proc_Final:

If Not rst Is Nothing Then
On Error Resume Next
rst.Close: Set rst = Nothing
On Error Goto 0: Err.Clear
End If
Set qdf = Nothing
Set dbs = Nothing

NewSuffixInMonth = Format(lngResult, "000")
Exit Function

Err_Catch:
MsgBox Err.Description
Resume ProcFinal

End Function
Nov 23 '05 #2

P: n/a
Kd wrote:
I have the OrderNo set as the following
Right(Format(Date(),"yyyy"),1) & Format(Date(),"mm") &
"-"Format([OrderNo],"000")+1
This works great until
I get to a new month and need the numbers to reset like the following
example
512-001
Anybody have any thoughts to point me in the right direction,got stuck
with making updates to someone elses created database and getting
frustrated.

Any thoughts or ideas would be appreciated
Thank you


How is OrderNo being incremented curretly?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 23 '05 #3

P: n/a
Kd


=DMax("[OrderNo]","[tblOrder]")+1

Nov 23 '05 #4

P: n/a
Kd wrote:
=DMax("[OrderNo]","[tblOrder]")+1


So you simply need to add a criteria to the DMax statement that will cause it to
start over each month.

=Nz(DMax("[OrderNo]","[tblOrder]", Format(RecordDate, "yyyymm") = Format(Date(),
"yyyymm")), 0)+1

Obviously you need a field like [RecordDate] in your table that provides a
timestamp for when the record was created. Given your current setup I assume
you have such a field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 23 '05 #5

P: n/a
Kd
Yes I have a field in table OrderDate
Thank you for your input

Nov 23 '05 #6

P: n/a
Kd
Ok have Order number working fine in form
but I would like this number511-001
to be the Pk on the order table

Nov 25 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.