Increment numbers for month, restart on new month

[Debbie37]

Work Order#s are determined by the two-digit month, two-digit year and an incrementing number (based on the number of repairs for that month). For example, the first repair for this month would be 03021, then 03022, 03023, 03024, etc.(allowing at a glance guesstimate of month/year and placement in month of repair). I have two separate fields on my form: one is a date field (mmyy)[WrkOrdrDate] and the other a number field [WrkOrdr#]. How do I - 1. get the number field to auto-increment and 2. have it restart on the first day of the month. In playing around I could get it to increment by one for the current record, not for the recordset. Any ideas? Have a GREAT day!

 

[mac318]

First, to increment the counter. Since you store the counter(WorkOrder#) as a numeric field, your can add one to [WrkOrdr#] = Max([WrkOrdr#]).

Use error trapping to tell you when the date changes months:

[WrkOrdrDate] + 1 will add a day to the WrkOrdrDate field. This will work well until you add a date which does not fit the month; i.e., September 31, which will cause an error. In the error trapping routine, roll the [WrkOrdr#] over to the new month.

mac

 

[TruthAndBeauty]

Thereare multple approaches, here's one

Use the DatePart function to get a count of records associated with the current month/year.
DatePart function description:
OrderDate DatePart Function This expressionReturns
3-Jun-93 DatePart("m",[OrderDate]) 6 (month of year)
28-Mar-92 DatePart("yy",[OrderDate]) 92 (two-digit number of year)


Your record count would be based on:
DatePart("m",[OrderDate]) = DatePart("m",Date())
DatePart("yy",[OrderDate]) = DatePart("yy",Date())

Your next record# is the record-count +1

---------------------------------------------------------------
Aside-Suggest your db avoid dependency on 2 digit years.


Good luck