Resetting a numeric field to 1 at the beginning of every month

[AOGFADUGBA]

HI ALL. I HAVE A TABLE WITH AN INDEX FIELD CALLED DEPTNO, WITH ANOTHER NUMERIC KEY CALLED PVNO.
THE PVNO INCREASES BY ONE BY AN AFTER-UPDATE EVENT OF A PDATE FIELD:

Private Sub PDate_AfterUpdate()
If Me.NewRecord = True Then
Me.PVNo = Nz(DMax("PVNo", "CedisPayment"), 0) + 1
End If
End Sub

WHAT I WANT NOW IS FOR THE PVNO TO RESTART AT THE BEGINNING OF EVERY MONTH. AND THE DEPT NO TO RESTART AT THE BEGINNING OF EACH YEAR. IS IT POSSIBLE WITH ACCESS?
THANKS

 

[HiTechCoach]

TIP: All CAPs is considered yelling/screaming. It is also harder to read. Please avoid all CAPs in the future.

Yes it can be done. You will need to add criteria the the Dmax().

Private Sub PDate_AfterUpdate()

   Dim datMthStart as Date
   Dim datMthEnd as Date


   If Is Null(Me.PVNo) and IsDate(Me.PDate) Then

       datMthStart = DateSerial(Year(Me.PDate), Month(Me.PDate), 1)
       datMthEnd  = DateSerial(Year(Me.PDate), Month(Me.PDate) + 1, 0)
       ' find max PVNo for the current Month and add 1
       Me.PVNo = Nz(DMax("PVNo", "CedisPayment", [PDATE] bewteen #" & datMthStart  & "# and #" & datMthEnd  & "#"), 0) + 1
   End If
End Sub

The same can be applied to Dept No restarting each year.

For your notes:
The first day current month: DateSerial(Year(Date()), Month(Date()), 1)
The last day of the current month: DateSerial(Year(Date()), Month(Date()) + 1, 0)
The first day current year: DateSerial(Year(Date()), 1, 1)
The last day of the current year: DateSerial(Year(Date()), 12, 31)

 

[AOGFADUGBA]

Hi Techcoach and thanks so much for the code. But this is what i got when i inserted the code.

Private Sub PDate_AfterUpdate()
Dim datMthStart As Date
Dim datMthEnd As Date


If Is Null(Me.PVNo) and IsDate(Me.PDate) Then


datMthStart = DateSerial(Year(Me.PDate), Month(Me.PDate), 1)
datMthEnd = DateSerial(Year(Me.PDate), Month(Me.PDate) + 1, 0)
' find max PVNo for the current Month and add 1
Me.PVNo = Nz(DMax("PVNo", "CedisPayment", [PDATE] bewteen #" & datMthStart & "# and #" & datMthEnd & "#"), 0) + 1
End If
End Sub

Any idea what might be wrong.
Thanks

 

[AOGFADUGBA]

I changed the Is Null to IsNull, and the red colour went off
I also changed the bewteen to between and changed the me.pvno to me.[PVNo] but the red line still persist.
Please what am i doing wrong.
Thanks

 

[HiTechCoach]

I changed the Is Null to IsNull, and the red colour went off
I also changed the bewteen to between and changed the me.pvno to me.[PVNo] but the red line still persist.
Please what am i doing wrong.
Thanks

Sorry for the typos. I was typing it directly here where there is no was to test it.

Looks like I also left out the opeing Quites ) ") in the criteria parmater of the DMAX().

Private Sub PDate_AfterUpdate()

Dim datMthStart As Date
Dim datMthEnd As Date


If IsNull(Me.PVNo) and IsDate(Me.PDate) Then

     datMthStart = DateSerial(Year(Me.PDate), Month(Me.PDate), 1)
     datMthEnd = DateSerial(Year(Me.PDate), Month(Me.PDate) + 1, 0)

     ' find max PVNo for the current Month and add 1
     Me.PVNo = Nz(DMax("PVNo", "CedisPayment", "[PDATE] between #" & datMthStart & "# and #" & datMthEnd & "#"), 0) + 1

End If

End Sub

FWIW: I use similar code in some of my own applications. I know the method works.

 

[AOGFADUGBA]

Thanks Boyd worked fine.

 

[HiTechCoach]

You're welcome. Glad we could assist.

 

[AOGFADUGBA]

Hi
please it it possible for me to use this code to build an expression i a table field?
if yes how do i go about it.
Thanks a million

 

[HiTechCoach]

Hi
please it it possible for me to use this code to build an expression i a table field?
if yes how do i go about it.
Thanks a million

You could try creating a function that generates the next number. The function could be used in the expression builder.

I use this method a lot to get the next value. I have yet to have the need to do it at the table level. Curious, why do you need this at the table level?

 

[AOGFADUGBA]

Thanks Boyd. The vba code you helped with is for the addition form for a table. There is another table with the same form and fields. The first table is for actual payment while the second table is for pending payments. Now, records in pending payments are moved to payment table with an append query when payment is made, but there will always be a duplicate PVNo in the Payment table with the append query action. this is what i want to prevent. Hope it makes sense. Do you have a better idea of how to go about this?
Thanks