Query That Says "No Data" If There Are Zero Results

[BruceM]

That's quite interesting. I suppose it could be of some value if the idea
is to run queries independently of forms or reports, but if somebody is
using the database at that level I would think they know that an empty
recordset means no records match the criteria.

The OP first asked about a SELECT query, then mentioned forms and subforms,
then stomped off in a huff claiming discovery of a SELECT query method.
Maybe he meant a Union query ;-)

 

[BruceM]

My rudimentary liturgical Latin never evolved to understanding that level of
detail! They must have really drilled that stuff into you, because there
are plenty of things I remember studying but which I cannot recall in any
kind of detail now.

 

[Paperback Writer]

My apologies to all for being abrupt.

No point to go into details, but Ken Sheridan NAILED IT. I used a union
query.

And, BruceM, you are exactly right. I wanted at query that would perform
that operation without jacking around with my forms.

Here's how the whole thing went down.

1. I asked for a way to do this in a SELECT query.
2. An MVP said it couldn't be done. The MVP suggested to do it in the
form. SO... I asked about how it could be done in a form since he suggested
it but didn't tell how. He did manage to insert a lecture about how users
are supposed to interact with data (as though there was a holy manual written
on the subject and I was too stupid to not have read it).
3. Then came the reply from another MVP that my question was too vague. In
fact, my question was focused laser-like on EXACTLY what I wanted to
accomplish.

If you care to piece this together, I suggest that you look at the time
stamps for each message. You'll see that my record of events is precisely
what happened. It's a very common problem that people try to focus their
answers on what they know how to do, and not on the precise task at hand.

 

[Paperback Writer]

Unless I'm mistaken, but isn't a union query really a SELECT query?

SELECT *
FROM [Table 1]
UNION SELECT *
FROM [Table 2];

That's quite interesting. I suppose it could be of some value if the idea
is to run queries independently of forms or reports, but if somebody is
using the database at that level I would think they know that an empty
recordset means no records match the criteria.

The OP first asked about a SELECT query, then mentioned forms and subforms,
then stomped off in a huff claiming discovery of a SELECT query method.
Maybe he meant a Union query ;-)

 

[BruceM]

A union query is not a select query, and it is not an especially easy way to
go. Your original post asked only for an easy way to use a Select query. I
will admit to some skepticism that you came up with that on your own several
days ago, and are just now getting around to mentioning it.

Later you wrote: "How about if I'm using a subform?...
It would be great if I could make it say something like 'No Data Found' in
this instance.

I would have taken "it" to refer to the subform. You received several
suggestions about how to use Form events to accomplish what you want, but
you chose to take offense at Tom's remarks.

The MVP who pointed out that users should interact with data using forms or
reports, and not directly through queries, was stating a common design and
development principle. He also suggested using the No Data event of a
Report to accomplish what you wanted. His was the first reply, at which
point you had asked only about using a Select query. Perhaps he suggested a
report because he took a guess that you were viewing the data only, not
interacting with it. If you wish to allow users to interact directly with
tables and queries, that is your business. You can listen to the voices of
experience, or you can find out for yourself how quickly a lot of data can
be mangled or lost. My guess is that you will attempt to prove experience
wrong. Good luck.

 

[Bob Quintal]

=?Utf-8?B?UGFwZXJiYWNrIFdyaXRlcg==?=

Unless I'm mistaken, but isn't a union query really a SELECT
query?

SELECT *
FROM [Table 1]
UNION SELECT *
FROM [Table 2];

No, it is not. It uses two or more select queries as subqueries.

 

[BruceM]

It can also use something other than a query to, in effect, insert another
row into a recordset. For instance, a Union query can be used to Insert an
{All) row into a combo box: Row Source query

SELECT [ID], [TextField]
FROM Table1
UNION SELECT Null, "{ALL)" From Table1
ORDER BY [TextField]

To PaperbackWriter, in your example Table1 and Table2 may be, for instance,
data that is not properly normalized such as a separate table for each year.
The Union Query can present the data as if they are in a single table. Ken
Sheridan's example is somewhat analogous to my first example in that it
inserts a sort of dummy row when there are no records.

=?Utf-8?B?UGFwZXJiYWNrIFdyaXRlcg==?=

Unless I'm mistaken, but isn't a union query really a SELECT
query?

SELECT *
FROM [Table 1]
UNION SELECT *
FROM [Table 2];

No, it is not. It uses two or more select queries as subqueries.

 

[James A. Fortune]

You haven't actually shown me the better mousetrap, so I'm not beating any
pathways to your door just yet, but I'm glad to hear you arrived a solution
which suits you. As regards all this debate about whether a 'union query' is
a 'select query' I'm inclined to view this as a bit of semantic navel gazing
to be honest. Personally I'd refer to a 'union operation' but that's only a
personal preference. Even the term 'query' tends to be bandied about in a
somewhat cavalier manner. There are those from the SQL world who'd maintain
that what Access calls 'action queries' aren't queries at all in the true
sense. Also the term query tends to be used for what's more accurately
described as a 'query definition' (querydef) i.e. a 'saved query' rather than
a SELECT statement in SQL per se. At the end of the day it’s a case of
'chacun à son goût'.

As regards doing this in a query rather than through other means, when I said
'no one in their right mind would' I was of course egging the custard a
little for rhetorical effect. If you want to do it that way I've no great
objection; it’s a free world (not too sure about that, actually!) after all,
and I'm not really questioning the sanity of anyone who would do so. Lets
just say its an unusual approach which few of us would employ.

The way I'd do it would be to hide the relevant control or controls in the
event of there being no data returned, i.e. hiding a subform control or
subreport control if either of those are being used, or the relevant bound
controls on a form or report otherwise. In their place I'd show a label to
the effect that no data is returned. If opening a report which might not
return any data I'd normally use its OnNoData event procedure to handle this,
setting the return value of its Cancel argument to True and popping up a
message box to inform the user that there is nothing to report. The report
would not then open at all. If the report is opened with the OpenReport
method then its also necessary to handle and ignore the error which
cancelling the opening of the report will raise.

Ken Sheridan
Stafford, England

The plethora of platitudes set off my buzz phrase bingo alarm, but they
were, as far as I can tell, done in a novel way (if such can be said
about clichés) by beating a dead horse, as it were, making them very
entertaining. False alarm.

Our philosophies about how to deal with reports that contain no data
differ little. In fact, it's refreshing to see someone else who thinks
that the place to deal with reports that have no data is before the
report is opened.

James A. Fortune
(e-mail address removed)

Clichés are the lingua franca of the political world.

 

[James A. Fortune]

As a former landlord of mine would have put it, "You’re the cat's pyjamas,
James". That is meant to be a compliment BTW.

Ken Sheridan
Stafford, England

Thanks. I thought your Latin translation and OED citations were
delightful as well. The fact that you learned Latin so well is a credit
to you. The fact that you remember it after so many years is a credit
to your instructor.

James A. Fortune
(e-mail address removed)

Dim I As Integer
Let I = 5

 

[James A. Fortune]

I'm afraid I'm a bit of a 'word nerd'. Sad, I know. I put it down to being
Irish!

Regards,

Ken

You're a sesquipedophile? That's verbal abuse !

BTW, sesquipedophile is a word with insalubrious moral connotations I
totally made-up from:

http://www.merriam-webster.com/dictionary/sesquipedalian

Main Entry:
ses·qui·pe·da·lian
Pronunciation:
\ËŒses-kwÉ™-pÉ™-ˈdÄl-yÉ™n\
Function:
adjective
Etymology:
Latin sesquipedalis, literally, a foot and a half long, from
sesqui- + ped-, pes foot — more at foot
Date: 1656

1 : having many syllables : long <sesquipedalian terms>
2 : given to or characterized by the use of long words <a sesquipedalian
television commentator>

and

http://www.merriam-webster.com/dictionary/-phile

Main Entry:
1 -phil
Variant(s):
or -phile
Function:
noun combining form
Etymology:
French -phile, from Greek -philos -philous

: lover : one having an affinity for or a strong attraction to
<acidophil> <Slavophile>

I'd give the OED definitions instead, but my copy is at home.

James A. Fortune
(e-mail address removed)