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

Reset consecutive numbers at start of month

P: 62
I have a seperate table to generate consecutive numbers. Using "Dmax" to find the largest number to increment .
My problem is that I want the number to begin at 1000 at the start of each month, deleteing the previous numbers created in the table.

Table : tblNewNum
Field: NewNum

Januarys Contents of field would read ie. 1000, 1001, 1002, 1003 etc

How can I get the first number generated at the start of each month to be 1000?

Hope this makes sense and that there is a solution, I am using MS Access 2000 with XP.

Feb 2 '07 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 10K+
P: 12,359
What's your experience with coding?
Feb 2 '07 #2

P: 62
What's your experience with coding?
Limited, but am learning as I go along. I am not fazed by a bit of difficult coding even if I dont fully understand it. I tend to search the web for answers to my problems as and when I come across one.

Do you need any of the code I am using to generate the consecutive numbers?
Feb 2 '07 #3

Expert Mod 10K+
P: 12,359
I don't need the code you use to generate the number, I think I know what you did for that.

To do what you want, you're gonna have to run a check.

Expand|Select|Wrap|Line Numbers
  1. If Day(Date()) = 1 Then
  2.   ' Check to see if you've already run this for the month.
  3.   ' If No, then delete all records and insert one record with the value 999
  4. End If
To check if you've already run the delete query for the month you'll have to store the last month you ran the delete query in either the same table or in a different table.

Let me know if you need any more specifics.
Feb 2 '07 #4

P: 62
Sorry if I am being a bit thick! But if you could explain a little more I would be very grateful. It sounds as if you understand what I am trying to achieve, it is just the how and where.

Feb 2 '07 #5

Expert Mod 10K+
P: 12,359
What's you want to do is make another table that has one field and one record. The record will basically be the number of the month when the other table was last cleared.

So what you'll have is 2 tables.


Now in the same place that you generate the Auto Number, before everything else, you need to check if 1) It's the first of the month and 2) If the table has been cleared yet. This is checked by seeing if the month in the table is the same as the month of the current date.

If both of these are true, then it deletes all records from the 2 tables and inserts the value 999 into tbl_AutoNumber and the value of the current month into tbl_Month.

Expand|Select|Wrap|Line Numbers
  1. If Day(Date()) = 1 Then
  2.   If DLookup("LastMonth", "tbl_Month") = Month(Date()) Then
  3.     DoCmd.SetWarnings False
  4.     DoCmd.RunSQL "Delete * From tbl_Month"
  5.     DoCmd.RunSQL "Delete * From tbl_AutoNumber"
  6.     DoCmd.RunSQL "Insert Into tbl_Autonumber Values (999)"
  7.     DoCmd.RunSQL "Insert Into tbl_Month Values (" & Month(Date()) & ")"
  8.     DoCmd.SetWarnings True
  9.   End If
  10. End If
Feb 2 '07 #6

P: 62
Have tried the code but am getting a "Type Mismatch" warning re:

If DLookup("LastMonth", "tbl_Month") = Month(Date()) Then ........

It appears the error involves the Month(Date())

What have I forgotten to do? Is the Month(Date()) looking for the number of month ie. "1" or the word "January" in tbl_Month, or is it something else that I should have done?

Feb 3 '07 #7

P: 62
Further assistance please:

I thinh I have sorted the previous problem, thnank. But have now encountered a further one.
This was working but now, for some reason, does not:

It is the consecutive number using DMax to look up the greatest number used last! It has stopped looking for the greater number and each time I run the code I get the number generated : 1000 (it would actually read "02BP1000")

Here is the complete code I am using:

Expand|Select|Wrap|Line Numbers
  2. Dim strTable As String
  3. Dim Month As String
  4. Dim Month2 As String
  6. Month1 = Format([Date], "mm")
  7.  Month2 = Month1 - 1
  8.   Me.txtBrokerName = Me.cboBrokersID
  9.   Me.txtDealNo = Me.DealNo
  10.   Broker = Me.txtBrokerName
  11.   strTable = "tblNewDealNum" + Broker
  12.   strNum = Broker + "QiNum"
  14.    If IsNull(DMax(strNum, strTable)) Then
  15.              SQLtxt2 = "INSERT INTO " & strTable & " (" & strNum & ")  SELECT 999"
  16.                    DoCmd.RunSQL (SQLtxt2)
  17.                                 End If
  19.  If Day(Date) <= 31 Then
  20.   If DLookup("LastMonth", "tblCheckMth") = Month2 Then
  21.     DoCmd.SetWarnings False
  22.     DoCmd.RunSQL "Delete * From tblCheckMth"
  23.     DoCmd.RunSQL "Delete * From " & strTable
  24.     DoCmd.RunSQL "Insert Into " & strTable & " Values (999)"
  25.     DoCmd.RunSQL "Insert Into tblCheckMth Values (" & Month1 & ")"
  26.     DoCmd.SetWarnings True
  27.   End If
  28. End If
  30. Num = DMax(strNum, strTable) 'This does not appear to work!
  31.         NewNum = Num + 1
  32.         Me.txtDealNo = "" & Month1 & "" & Broker & "" & NewNum & " "
  33.         Me.DealNo = Me.txtDealNo
  34.         strMsg = Me.DealNo
  35.         Me.txtBrokerUpdate = Me.txtBrokerName
  37. MsgBox "Ticket Number " & strMsg & "."
  38.     SQLtxt1 = "INSERT INTO " & strTable & " (" & strNum & ")  SELECT " & NewNum
  39.          DoCmd.RunSQL (SQLtxt1)
  40.             DoCmd.GoToRecord acDataForm, "frmAddDeal", acNewRec
  41.                 cboBrokersID.SetFocus
Feb 3 '07 #8

P: 62

May have sorted it !!!!

I had the table "QiNUm" set to "Text" I have changed it to "Number" and it seems to be working.

Big thanks for all your help.

Feb 3 '07 #9

Expert Mod 15k+
P: 31,434

May have sorted it !!!!

I had the table "QiNUm" set to "Text" I have changed it to "Number" and it seems to be working.

Big thanks for all your help.

Just an idea, and I know it's a bit late, but if the data you get is to add a new value into your main table and that includes an indicator for the month, then you can use the same concept to get the value you need without recourse to a separate table at all.
As an illustration, say your main table has items with a PK made up of (including would work too, but for simple illustration...) MMMXXXX where MMM ==> 3 letters of Month & XXXX ==> 4 digit number starting from 1000.
Expand|Select|Wrap|Line Numbers
  1. Dim strPK As String
  3. strPK = Format(Date(),"mmm")
  4. strPK = strPK & _
  5.         Val(Right(Nz(DMax("[PK]", _
  6.                           "[tblMain]", _
  7.                           "[PK] Like " & strPK & " & '*'"), _
  8.                     "XXX0999"),4))+1
Feb 4 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.