Expression for form to find highest # less than a designated amount 1

[IDilbert6]

Our staff forgot to start renumbering batches for then new fiscal year. Now I want to start with batch 1 and auto-increment batch numbers up by 1 from there, while keeping the batches already in the database that start 600. I have used a formula in the past that searched for the Max and added one, but now I need to do that and ignore the batch numbers that start with 600 and higher. Here is the formula I used successfully before:
=IIf(IsNull(DMax("[BatchNumber]","[tblPVBatchLog]")),1,DMax("[BatchNumber]","[tblPVBatchLog]"))+1

How do I include the "but < 600" in here?

Thanks much.

 

[Remou]

This seems to me a recipe for disaster, however, this is how it works:
[tt]=IIf(IsNull(DMax("[BatchNumber]","[tblPVBatchLog]")),1,DMax("[BatchNumber]","[tblPVBatchLog]","[BatchNumber]<600"))+1[/tt]

 

[PHV]

Why not simply this ?
=Nz(DMax("BatchNumber","tblPVBatchLog","BatchNumber<600"),0)+1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IDilbert6]

Thanks so much, PHV! You simplified and corrected my expression in one fell swoop - it works perfectly now.