Dmax calculation, based on Date criteria

[jainkamleshd]

Hello,
I am new to this forum.

I want to find Max no (DO_Number) after a certain date, say 31/03/2019.



I am trying through below qry.

Expr1: Nz((DMax("DO_Number","tblDOOutward","[Our_DO_Date] > #31/03/2019#")),0)


On 31/03/2019, my Last DO_Number was 1678 but w.e.f. 1st April, i want to restart from number 1

Help shall be appreciated.
PS : I am not a professional programmer but learnt it from net/other sources..

Regards,
Kamlesh Jain

[Minty]

That looks pretty good to me - what problem are you getting ? Error message ? Wrong result ?

You do have some extra brackets, try ;

Expr1: Nz(DMax("DO_Number","tblDOOutward","[Our_DO_Date] > #31/03/2019#"),0)

[jainkamleshd]

Thanks for prompt reply.
Tried your query but its giving wrong result. It still giving max Do_Number as 1678 only. Sample data as under :

Our_DO_Date DO_Number
28/03/2019 1676
29/03/2019 1677
31/03/2019 1678
01/04/2019 1
02/04/2020 2

So my qry shall result = 2 and not 1678
Hope i am able to explain my problem.
Regards.
Kamlesh Jain

[Minty]

Hmm interesting - Is DO_Number a number or text ?
And is Our_DO_Date a date field and not text ?

Also try #2019/03/31# as the date...

[jainkamleshd]

Thanks Minty.

1. DO_Number is Number Field
2. Our_DO_Number is Date Field
3. We (In India) use date format as DD/MM/YYYY only.

Shall i try with :
[Our_DO_date] > #" & Format(Me!Our_DO_Date, "yyyy\/mm\/dd") & "#)
?

[Minty]

3. We (In India) use date format as DD/MM/YYYY only.

Shall i try with :
[Our_DO_date] > #" & Format(Me!Our_DO_Date, "yyyy\/mm\/dd") & "#)
?

Yes definitely - it may be the date formatting is tripping you up.
We have the same issues with UK dates.

[jainkamleshd]

Well i tried below :

Me!DO_Number = Nz((DMax("DO_Number", "tblDOOutward", [Our_DO_Date] > Format(#3/31/2019#, "yyyy\/mm\/dd"))), 0) + 1

But its giving result as 1 only wheras result shall be 2+1 = 3
What can be the cause ?
Help please.

[Minty]

I have just tested with both DMax and DMin on a sample table in the immediate window, and got the correct results;

? DMax("[Empl_Numb]","Table1","StartDate > #" & Format (#16/09/2016#,"yyyy/mm/dd") & "#" )
 10200 

? DMin("[Empl_Numb]","Table1","StartDate > #" & Format(#16/09/2016#, "yyyy/mm/dd") & "#") 
 10047

So something else is wrong here.

Get rid of the Nz function, and remove the extra brackets. Try the DMax in the immediate window first to get the right result.