ACCESS 2000 and Visual Basic

  • Thread starter Thread starter SakuraSakura
  • Start date Start date
S

SakuraSakura

I was wondering if you can help me on a problem that I am
having. I am creating a database using Access 2000. I am
dealing with spatial queries. The user enters a x and y
coordinate. From that I see if there are any locations
close to them within a 5 mile radius. If there is that
great, but if there isn't I have to extend it to a 10 mile
radius. Essentially once the query returns at least one
dentist I am happy. Until then I have to keep expanding my
radius. PROBLEM: How do I know how many rows are being
returned in the query. I have tried a couple things and I
hav emade no progress. My database is called Dentist, my
query is called Results, and the form that accesses that
query is called UserSearch..just in case they need to be
referenced in the code. Your help is greatly appreciated.
 
Hi,



SELECT COUNT(*) FROM TableName WHERE condition

returns the number of records in

SELECT whatever FROM TableName WHERE condition


Hoping it may help,
Vanderghast, Access MVP
 
Can this be done in Visual Basic. I think that is has
something to do with Dim rs AS DAO.Recordset you help is
greatly appreciated?
 
Hi,


With DAO, sure. You have CurrentDb (in Access) or a database object, db,
in VB6, let say, so:


? db.OpenRecordset("SELECT COUNT(*) FROM myTable WHERE condition
").Fields(0).Value



If you deal with a simple table (no join) or through a saved query, you can
use DCount, if from Access:

? DCount("*", "TableNameOrSaveQuery", "condition here")

the third argument is optional ( forget it if there is no condition to
apply).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top