Search for Next Available Number

[Christine Boissoin]

I have a table with the following values:

182.11
182.12
182.13
182.16

I want my query to return 182.14 and/or 182.15 as available numbers for assignment. Is there any way to find missing numbers within a range of numbers?

Thanks


CB

[orange]

Could you provide context? What's with 2 decimal places?

[Christine Boissoin]

The numbers actually refer to an IP range - the "182." can be ignored. If the query could return just the 15 or 16 that would be sufficient.

Thanks.
cb

[orange]

I'm not fully understanding your situation and how it relates to a query. But, from the info supplied, I think you could create a function/subfunction to identify missing numbers.
General logic.(not tested)

sub testit()
dim mStr as string
Dim sMin as long
Dim sMax as long
sMin = DMin("YourField","YourTable")
sMax = Dmax("YourField","YourTable")

For x = sMin to sMax
if dcount("*","yourtable","YourField = " & x) = 0 Then
mstr = MStr & x & ","
endif
next x

MsgBox " Values NOT in your table " & mStr

End Sub

good luck

[Christine Boissoin]

Thanks - this works perfectly.
CB

[orange]

Glad to help.