-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Somebody else had a similar "brute-force" solution - here's another.
It's final result is a string of comma-delimited numbers representing
the missing permit numbers. You may wish to do something else w/
these numbers - you'll have to change the code to suit your purposes.
The Replace() function is not available in Acc97 and earlier versions.
There is a similar function in the Neat Code example database
available on the Microsoft download website (watch out for line-wrap):
http://support.microsoft.com/default.aspx?scid=kb;en
us;177972&Product=acc97
You'll have to change the SQL_PERMITS string to match your table/field
names.
You should put in error traps.
=== air code ===
Function MissingPermits() As String
' return a comma-delimited string of
' the missing permit numbers, on, or after,
' the indicated date
const SQL_PERMITS = "SELECT PermitNo " & _
"FROM Permits " & _
"WHERE PermitDate >= |1 " & _
"ORDER BY PermitNo"
dim db as dao.database
dim rs as dao.recordset
dim strSQL as string
dim strMissing as string
dim lngPrevious as long
' Put in the indicated date
' Using Acc2002
' If Replace function needed in Acc97 see
' ReplaceStr() in ntcode97.exe on MS download site.
strsql = Replace(SQL_COUNT,"|1","#1/1/2003#")
set db = currentdb
set rs = db.openrecordset(SQL_PERMITS)
' initialize - start w/ the 1st available no.
' assumes this no. is a "good" starting
' no. for the indicated date.
if not rs.eof then
lngPrevious = rs!PermitNo
rs.movenext
end if
do while not rs.eof
' iterate thru other records
' finding all missing permit nos.
if rs!PermitNo - 1 <> lngPrevious then
strMissing = strMissing & _
FillNumbers(lngPrevious, rs!PermitNo) & ","
Endif
lngPrevious = rs!PermitNo
rs.MoveNext
loop
' Get rid of trailing comma
if len(strMissing)>0 then
strMissing = Left$(strMissing,len(strMissing-1))
end if
MissingPermits = strMissing
End Sub
Function FillNumbers(lngStart as long, lngEnd As long) as string
' return a comma-delimited string of sequential numbers
' between lngStart and lngEnd range, excluding lngStart & lngEnd
dim i as long
dim strResults as string
for i = lngStart + 1 to lngEnd - 1
strResults = strResults & i & ","
next
' drop trailing comma
if len(strResults)>0 then
strResults = left$(strResults, len(strResults)-1)
end if
FillNumbers = strResults
End Function
== end air code ==
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP57drYechKqOuFEgEQJneQCgnXIvgmei8k5sc5tOFaw1P2 TM47wAoPAx
op7c+boR7M47MCVFPV2SrjX9
=pXRS
-----END PGP SIGNATURE-----
Annette Massie wrote:
The county park system has many individual parks and many park workers
who have their own books of permits. To have the program increment
won't really work because their could be any range of permits sold in
any given day at any location by any worker.
