Is there a way to exclude part of a query?

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi All,

Is it possible to exclude part (or to avoid running) if there is no
available data in the query's source or one of its sources?

TIA

Alp
 
Well, you could run queries against each table used in your query and see if
they have any data. But that almost seems self-defeating - running a bunch of
queries to determine if your query is going to return data.

Can you describe why you think you need to do this? What problem are you trying
to solve? There may well be a better solution.
 
Well John, I do think there should be a better solution maybe running the
query in VB. Reason is; due to circumstances there may not be any entry in
one of the tables under a query causing it to fail to output any data. This
would then effect the outcome of the calculation based on that query's
result on both a form and a report.

What I need is to be able to get a result regardless of the fact that some
fields are not available, i.e. the table has no records but the other does.

Clear as mud? Let me know if I need to write more on this for clarification.

Alp
 
Hi,

PMFJI...
Reason is; due to circumstances
there may not be any entry in
one of the tables under a query
causing it to fail to output any data.
Click to expand...

If a Left Join doesn't help in this situation, then maybe running a
DCount("*","tblYourTable") > 0 in your VBA first would help.
 
Ok, then you can use several methods to find out if a table has any records.

For tables in the database (not linked) you can use the recordcount property,
which is fairly fast.

currentdb().TableDefs("YourTableName").RecordCount

or you could use the DCount function.

DCount("*", "YourTableName")

So you could test these values in VBA code and then choose to open the report,
query, or form.

IF DCount("*","YourTableName") > 0 then
'Do Stuff
End If
 
Hi John,

Thanks for the advice but all tables are linked tables from 2 external mdb's
that hold only these tables. Both reside in the same location as the main
application mdb.
What can be done now?

Alp
 
Use the DCOUNT function. That works with linked tables. Sorry, I didn't mean
to suggest that DCount only worked with local tables.
 
Back
Top