Solved DMax increment numbering not working

[Silver_surfer]

Hi guys,
I'm new to Access, and is now in the process of editing Northwind templates to suit up my needs
However, right now I'm stuck at making my own version of increment number
Nothing too fancy actually, I just need the number to restart at the start of December

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.[NoP3C] = DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "30 / 11 / " & Year(Now) & "#") + 1
End If
End Sub

But all I got is null. Any ideas? And sorry for my english

 

[plog]

Do you have any records with an Expected date > 11/30/2020?

If not, get some. Or write some code to handle no records meeting the criteria.

 

[Silver_surfer]

Okay, so I've tried to trap the null value using Nz
Now I get the number, but its not increment
So the first record got number 1, and so is the second, third...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "30 / 11 / " & Year(Now) & "#"), 0) + 1
End If
End Sub

Or is it another code that you meant?
Sorry for such a simple question, but I'm totally new in this, so please bear with me ☹

 

[vba_php]

Surfer, you said:

I just need the number to restart at the start of December

but your code is attempting to populate the textbox with the *highest* number from the field:

Me.[NoP3C] = DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "30 / 11 / " & Year(Now) & "#") + 1

but you took care of the NULL return by using NZ(). but then, your next code:

Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "30 / 11 / " & Year(Now) & "#"), 0) + 1

is returning 1 for every record more than likely because NZ() is returning ''0'' and you're adding ''1'' to the result of that NZ() function. And NZ is returning ''0' more than likely because you have no records that have [expected date] > 30/11/2020. That's exactly why plog said what he did in response to you.

 

[Silver_surfer]

Sorry my bad
I've just aware of the logic in my code about same time as your post , fool me
Thanks for pointing out

So I've changed the code to

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]< #" & "30 / 11 / " & Year(Now) & "#"), 0) + 1
End If
End Sub

But I can't make it restart at 1 when [Expected Date] is 1st December
It just stuck at the highest number and keep repeating it
Please help ☹

 

[Galaxiom]

Have you tried constructing the date as mm/dd/yyyy ?

 

[Silver_surfer]

Do you mean the [Expected Date]?
It was formatted to Short Date, and the default is dd/mm/yyyy, per my region
Where do you think the problem is?

 

[vba_php]

Where do you think the problem is?

Surfer, honestly I think my previous post explains it quite well. As far as what I can offer, I've done all i can i think. sorry.

 

[namliam]

Excuse you me, for one VBA_PHP is along the right path... butyou have an added problem
For one short date is a display thingy, you are working in code and thus need to work with the code. Short date DISPLAYS in DD/MM/YYYY, however the database and code need US Formatting as in MM/DD/YYYY. IF you are lucky the database may recognize 30/11 to not be a valid MM/DD and actually fix it for you, but eventually you run into problems with dates like 05/11 May 11th or Nov 5th?

Furthermore you have a formatting issue. DD/MM/YYYY may be ok, however DD / MM / YYYY is not.

Lastly year(now) makes it "year(now)" as in 2020:

?"[Expected Date]< #" & "30 / 11 / " & Year(Now) & "#"
[Expected Date]< #30 / 11 / 2020#

?"[Expected Date]< #" & "30 / 11 / " & Year(Now) - iif(month(now()) < 12,1,0)& "#"
[Expected Date]< #30 / 11 / 2019#

so
1. Remove the spaces
2. Format US style
3. fix your logic
4. Best solution, remove this stupid functionaly and use a proper key.

 

[Silver_surfer]

Surfer, honestly I think my previous post explains it quite well. As far as what I can offer, I've done all i can i think. sorry.

Actually I've correcting it
The increment work right now, but I can't make it restart at 1st December
It just stuck at highest number and keep repeating it
Any help?

 

[Silver_surfer]

Excuse you me, for one VBA_PHP is along the right path... butyou have an added problem
For one short date is a display thingy, you are working in code and thus need to work with the code. Short date DISPLAYS in DD/MM/YYYY, however the database and code need US Formatting as in MM/DD/YYYY. IF you are lucky the database may recognize 30/11 to not be a valid MM/DD and actually fix it for you, but eventually you run into problems with dates like 05/11 May 11th or Nov 5th?

Furthermore you have a formatting issue. DD/MM/YYYY may be ok, however DD / MM / YYYY is not.

Well, I've tried to change 30/11 to 11/30, and still not working

Lastly year(now) makes it "year(now)" as in 2020:

?"[Expected Date]< #" & "30 / 11 / " & Year(Now) & "#"
[Expected Date]< #30 / 11 / 2020#

?"[Expected Date]< #" & "30 / 11 / " & Year(Now) - iif(month(now()) < 12,1,0)& "#"
[Expected Date]< #30 / 11 / 2019#

Actually that's what I want
So for every record where [Expected Date] is before 1 December this year, I want the number to keep increment
At 1 December, the number restart at 1, and then keep increment until 30 Nov next year
I hope I made myself clear

4. Best solution, remove this stupid functionaly and use a proper key.

Any ideas on what and how?
I've searching the web, and it seems almost all of them recommend using DMax()+1

 

[namliam]

4. why do you need this stupid increment per year? Probably to count the number of orders in a year or products or something along those lines.


3. really? Guess I must make it more clear.
I think you want this, using > 2019 instead of < 2020.... as anything and everything is less than 11/30/2020 thus getting stuck at the max from any year.
"[Expected Date]> #" & "30 / 11 / " & Year(Now) - iif(month(now()) < 12,1,0)& "#"

 

[Silver_surfer]

4. why do you need this stupid increment per year? Probably to count the number of orders in a year or products or something along those lines.

Well, I need to give my "Purchase Order" a sequential numbering, so I guess can't use DCount
And it needs to restart at each December for technical reasons (workplace reasons, not Access)

3. really? Guess I must make it more clear.
I think you want this, using > 2019 instead of < 2020.... as anything and everything is less than 11/30/2020 thus getting stuck at the max from any year.
"[Expected Date]> #" & "30 / 11 / " & Year(Now) - iif(month(now()) < 12,1,0)& "#"

I see, was confused with the '<' before
My bad, English isn't my first language

So the code would be

If Me.NewRecord Then
    Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "30 / 11 / " & Year(Now) - IIf(Month(Now()) < 12, 1, 0) & "#"), 0) + 1
End If

Am I correct?
I've tried it before, it worked for incremental number
But still won't restart at 1st December ☹

 

[namliam]

technical reasons? I understand december 1st as it probably is related to the financial year. Or some simular logic
But please explain technical reasons? What possible technical reason could force you to use this nasty structure?

If Me.NewRecord Then
    Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "11/30/" & Year(Now) - IIf(Month(Now()) < 12, 1, 0) & "#"), 0) + 1
End If

How are you testing the restart after December 1st?

 

[Silver_surfer]

technical reasons? I understand december 1st as it probably is related to the financial year. Or some simular logic
But please explain technical reasons? What possible technical reason could force you to use this nasty structure?

When PO is expected for December, then it only can be ordered by Customer in January
So the rules required the PO number to start from 1 when Expected Date past 30 November
As I said, it's my workplace reason, not Access
Sorry if my wording is confusing

If Me.NewRecord Then
    Me.[NoP3C] = Nz(DMax("[NoP3C]", "Purchase Orders", "[Expected Date]> #" & "11/30/" & Year(Now) - IIf(Month(Now()) < 12, 1, 0) & "#"), 0) + 1
End If

How are you testing the restart after December 1st?

I tried to write a bunch of dummy record using the form
And changed my system time to after 1st December

Edit: IT WORKS! Thank you so much
Turns out I haven't changed the date format
From 30/11 to 11/30
My bad then, thanks for the help everyone, especially namliam

Can I mark this thread as SOLVED here? How?