the next available number

[yonner1]

hello, i am going to try to make this very simple

Lunch Status Lunch Code

Paid
Reduced
Free

if the paid #'s are between 1000 and 1999. how do i write a query that everytime i click on the lunch status and i pick paid from the list it will return the result of the next available number between 1000 and 1999 in the lunch code table. sometimes the number are not consecutive because people have been deleted from the paid group into another group, but i want the next available number to come up. i hope i made myself clear.
thank you. yonner1

 

[sfm6s524]

Not sure I understand enough to get you a concise solution, but you'll probably need to use the DMax function to keep it simple. As in: nz(DMax("[Paid No]","tblLunchCode&quot) + 1.

Ken

 

[jiqjaq]

See thread for Displaying next available unused number
[h]thread701-626726[/h]