Is there a way to exclude part of a query?

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
 
J

John Spencer (MVP)

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.
 
A

Alp Bekisoglu

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
 
C

CSmith

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.
 
J

John Spencer (MVP)

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
 
A

Alp Bekisoglu

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
 
J

John Spencer (MVP)

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