Dmax reset

[Abacus]

Hi,

I have a system where I have joined fields together to make a unique numbering system:

QuoteYear / QuoteMonth / Quoter / QuoteNo
2019 11 SC DMAX Increment

Joined together it results are 2019-11-SC-0001

This works great however what I would like to do is have the QuoteNo field go back to 1 after a new month followed obvioulsy by the year change.

I would also like this for each member of the sales staff. So if SC starts off at 2019-11-SC-0001 the ML would start 2019-11-ML-0001

I tried a sample before putting into my database and it seemed to work however no in the main system the DMAX just rolls on regardless for everyone.:banghead:

My code is on current:
If Me.QuoteNo = 0 Then
Me.QuoteNo = Nz(DMax("QuoteNo", "Quote Register", Format(QuoteMonth, "mm") = Format(Date, "mm")), 0) + 1
Me.QuoteNo = Nz(DMax("QuoteNo", "Quote Register", Format(QuoteYear, "yyyy") = Format(Date, "yyyy")), 0) + 1
End If

Can anyone assist.

I'm not an experienced programmer so any help would really be appreciated.

 

[theDBguy]

Hi. Don't you want to combine all the criteria in one DMax()?

 

[Abacus]

Hi

Yes if I could that would be great, sorry I didn't know that would be possible. Could you provide an example I could try and work with, thanks

 

[theDBguy]

Hi

Yes if I could that would be great, sorry I didn't know that would be possible. Could you provide an example I could try and work with, thanks

It might look something like this.

Me.QuoteNo=Nz(DMax("QuoteNo","Quote Register","Staff='" & Me.Staff _
& "' AND QuoteMonth=" & Month(Date()) _
& " AND QuoteYear=" & Year(Date())),0)+1

 

[Abacus]

Thank you so much, I'll give this a try and let you know how I get on.

 

[theDBguy]

You're welcome. Good luck.