How do you display accounts not found after performing a query ?

  • Thread starter news.optusnet.com.au
  • Start date
N

news.optusnet.com.au

Hi I am new to Access,

I have two queries:

(A) I would like to know you get access to display details of numbers
not displayed after performing a query on a certain range of numbers,
query only seems to advise "X" number found, is there a way you can
display either a list of numbers found &/or both numbers found/not found ?

(B)After identifying the (A) above can you create a form with a button
to link to this query/code.

Thanks in advance.
 
A

Allen Browne

To display the numbers that are NOT present, the numbers have to come from
somewhere. If you have a table of all the suitable numbers, you can use
that; if not, you will have to create a table containing all the possible
numbers.

Once you have that, you can use the Unmatched Query Wizard (when you go to
create a new query) to return all the numbers from the complete table that
are not in the earlier query you created. (Basically it's an outer join
where the key field is null in the original query.)

If you need to programmatically populate a table with lots of records, this
function will do it. Create the table named "MyTable", with one Number field
named MyID.

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("MyTable", dbOpenDynaset)
For lng = 1 To HowMany
rs.AddNew
rs![MyID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function
 
Top