How to extract value

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

Ok!

I will try to explain my request!!!!!

I have a table with number from 1 to X

Some number are missing because of deleted records. Is there a function to
get all the numbers missing in a range. For exemple, I want to know in
this table the records missing between the number 1 and 50.

Thanks!
 
Create a worksheet in Excel with a column of numbers from 1 to 50. Link to this
worksheet from Access. Use the Unmatched query wizard to return all the numbers
in the Excel table that are not in your Access table.
 
I have a function I use for this purpose. A posssible call would normally be
the condition within an IF statement, something like....

IF
DMissing("YourIntegerFieldName","YourTableName","SomeWhereClause",MissingVal
ues()) THEN
FOR ct=1 TO UBound(MissingValues()
'do something with each MissingValue(ct)
NEXT
ENDIF

Here's the function...

Public Function DMissing(Expr As String, Domain As String, Criteria As
String, MissingValues()) As Boolean
'function returns false if no missing values are found
'function returns true if missing values found
'the missing values are returned in MissingValues()
'only works when Expr refers to an integer field
'all arguments must be included in a call

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Counter As Long
Dim Big As Long
Dim Small As Long
Dim MissingValuesUB As Long
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

'get the largest and smallest values
rs.MoveLast
Big = rs.Fields(Expr)
rs.MoveFirst
Small = rs.Fields(Expr)

'loop through the recordset ignoring matches but storing all others in
MissingValues
DMissing = False
MissingValuesUB = 0
For Counter = Small To Big
If rs.Fields(Expr) = Counter Then
rs.MoveNext
Else
MissingValuesUB = MissingValuesUB + 1
ReDim Preserve MissingValues(MissingValuesUB)
MissingValues(MissingValuesUB) = Counter
DMissing = True
End If
Next

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Function

HTH
Sam
 
Back
Top