DMax Problem

[DS]

I have a series of numbers 1,2,3,4,etc.
I'm trying to number records using DMax to find the next available
number, DMax only gives me the highest number, DMin gives me the lowest
number. I need something to find the first number that is missing in the
series and then use that number. Is there a DNext or something that I
can use?
Thanks
DS

 

[Ken Snell \(MVP\)]

How do you and ACCESS know when a number is available versus not available?

 

[DS]

How do you and ACCESS know when a number is available versus not available?

Good Question.
I don't know! Can't Access look intelligently for the next number in a
Series. Such as using 4 when it searches this series. 1,2,3,5,6,7
Thanks
DS

 

[Ken Snell \(MVP\)]

So the definition of "available" would mean that the number does not exist
in the table yet, is that it?

Note: it's critically important that you define how to identify when
something is/is not available, otherwise it'll be essentially impossible to
program a query or code to do it.

Tell us more about the details of your setup and the context in which you
want to identify/use the next available number. Note that a more usual way
to do what you seek is to have a table of sequential numbers with a field
that is a yes/no field in the record, where the yes/no field is set to True
if used and False if not used (or vice versa). This setup makes doing what
you seek much easier, as you are reading "existing" data and not trying to
find "missing" data.

 

[Douglas J. Steele]

See whether my April, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[DS]

See whether my April, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Thanks Doug,
That did it! The Access Answers thing is pretty interesting. I never
knew that it exsited. Thanks for pointing thatout as well/\!
DS

 

[DS]

So the definition of "available" would mean that the number does not exist
in the table yet, is that it?

Note: it's critically important that you define how to identify when
something is/is not available, otherwise it'll be essentially impossible to
program a query or code to do it.

Tell us more about the details of your setup and the context in which you
want to identify/use the next available number. Note that a more usual way
to do what you seek is to have a table of sequential numbers with a field
that is a yes/no field in the record, where the yes/no field is set to True
if used and False if not used (or vice versa). This setup makes doing what
you seek much easier, as you are reading "existing" data and not trying to
find "missing" data.

Thanks Ken, I see what you mean. Doug came up with something that
works. I just thought that it would be simpler! Once again thank you
for your help, you pointed out logic that I was completely missing!
DS