Dmax reset (1 Viewer)

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.

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

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

Abacus said:

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

Click to expand...

It might look something like this.

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

You're welcome. Good luck.