Getting greatest number based on conditions from records using DAO

[justinwright]

I searched and couldn't find anything, but perhaps I'm just inept at searching.

The fields I have that are relevant are:
Section - Number, Long Int
Period - Text
DepNum (deposit number) - Number, Long Int


I wanted to be able to have a certain set of conditions to find out the next deposit number by opening a recordset on the data (tblDeposits table).

Say Section "1" is in the "November 2010" period (financial month) with DepNum 1, 2 and 3. I wanted to make the "on lost focus" event for the cbo selection for Section look up the greatest number for the period and section (hidden text box), so I can then add 1 to it to get the next deposit number (in the aforementioned example, the text box would have "3" in it so I could then add 1 to it and have DepNum4 as the next deposit).

I assume if there are no deposits (say for the first deposit of the period), I can use like:

Me.txtDepNum = Nz(rs!DepNum, "1")



to specify.

As always, any help is much appreciated by the guy that's always poking people for help!

Justin

[ajetrumpet]

Justin,

people do this sort of thing by using dmax() and dcount(). eg. -

me.tbox = iif(dcount("field", "table") = 0, 1, dmax("field", "table") + 1)

[justinwright]

Okay, that makes sense, but how would you do it given those constraints? I see where it would return the greatest number in the field, but there will be 3 sections using the db with different deposit numberings, plus it needs to read only the current period (stored in another table, but on load txtPeriod is filled with that stored value on the same form so each added item into tblDeposits has the field for Period).

[ajetrumpet]

read the help article on those functions. they give the syntax. e.g., they have 3 parts:

(field, table, criteria)

remember that MS documentation specifies optional code and syntax by enclosures in [].

example - look up max dep number for last month, the mo of november, and give appropriate NEW dep number:

=iif(dcount("deposit", "table", "[datefield] > #10/31/2010#

AND [datefield] < #12/1/2010#") = 0, 1, 

dmax("deposit", "table", "[datefield] > #10/31/2010#

AND [datefield] < #12/1/2010#") + 1)

does that make sense?

[justinwright]

Yes it does, thank you very much for your help!