Query That Says "No Data" If There Are Zero Results
Paperback Writer
the query finds no data that matches the criteria?
Thanks!!!
Arvin Meyer [MVP]
message news:DE7D1A9E-2A2D-4FF8...@microsoft.com...
> Any easy easy way to make a Select Query say that there are no results
> when
> the query finds no data that matches the criteria?
No. If you base a report on that query, you can use the NoData property or
event, Queries are typically not a direct par of the user interface. Users
are supposed to interact with data ONLY through forms and reports and NEVER
through tables or queries. Unfortunately it is very easy for users to ruin
lots of data quickly, if you allow them to see/use data directly.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Tom van Stiphout
<Paperba...@discussions.microsoft.com> wrote:
No.
But depending on your situation you can handle that in your form or
report.
Sorry, vague question -> vague answer.
-Tom.
Microsoft Access MVP
Paperback Writer
My trouble is that I have a subform (based on a query) that disappears when
there is no data results returned.
It would be great if I could make it say something like "No Data Found" in
this instance.
Jim Franklin
in the form's recordsource.
For example, if the form object used in the subform is bound to the query,
put the following in the form's On Load event:
if me.recordsetclone.recordcount=0 then
'do the things I want to do here
end if
Hope this points you in the right direction!
Jim
"Paperback Writer" <Paperba...@discussions.microsoft.com> wrote in
message news:E92082BB-8E99-4B51...@microsoft.com...
Paperback Writer
into a little box that you can get your brain around, but that's how the real
works sometimes.
I figured out how to do it -- Inside a query, no less. Now, it works in all
my forms and reports. I don't have to worry about ticking any boxes in all
my various reports and forms. I fixed it at the source -- Which was the
point of my question.
Thanks for nothing, Tom!!!
fredg
Glub.. glub.. glub..
That sound you are hearing is the toilet flushing any future posts of
your down the drain.
Your reply to Tom is obnoxious. What may have been clear and simple to
you might be unclear and complex to someone with lot's more Access
experience than you have.
Let's see. You had 3 replies that it couldn't be done in a query,
and now you claim to have successfully done it ... except you are
self-centered enough to not even bother to tell the rest of us how you
did it. Why should anyone ever attempt to help you when you have no
thought of sharing your wonderful problem solving abilities and
helping others? Can it be that it's not our brains that are too small,
but rather that your head is too big?
Goodbye.
Plonk!!!
h
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Tom van Stiphout
<Paperba...@discussions.microsoft.com> wrote:
The "diappearing subform when no data" information is an example of
too little information in your original post. Subforms typically don't
disappear but simply display zero rows.
-Tom.
Microsoft Access MVP
Paperback Writer
attracts flame."
I responded to Tom with all the respect and attitude he deserved with his
flip response to my question.
Again, my original question wasn't vague or misleading in any way. It was
direct, and very precise.
And, I did figure out how to make the query work.
John Spencer
your posting, but my knowledge of Access is that what you want cannot be done
in a query. The query either returns records or it returns no records.
If you are using the query as the source for a form or report then I can
envision various ways to get NO Data to appear. But if I am directly running
a query I am stuck.
So, can you share your solution. Obviously, I am missing something.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
BruceM
query to produce output is if it returns at least one record.
For future reference you can lay off the attitude, even if you feel your
one-sentence question (which is not as precise as you seem to imagine)
deserved more respect.
"Paperback Writer" <Paperba...@discussions.microsoft.com> wrote in
message news:C86F3C51-E3DA-4537...@microsoft.com...
James A. Fortune
> How about if I'm using a subform?
>
> My trouble is that I have a subform (based on a query) that disappears when
> there is no data results returned.
>
> It would be great if I could make it say something like "No Data Found" in
> this instance.
I use the following module function for subforms/reports:
Public Function DNoRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset
DNoRecords = True
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
DNoRecords = False
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function
If I determine that a subform is not going to have any records, I change
the SourceObject of the Subform control to a form with a label that
simply tells the user that no records match the criteria. It is also
easy to reverse the function logic to something like DHasRecords() if
DNoRecords() gets confusing.
James A. Fortune
MPAP...@FortuneJames.com
BruceM
you did this in a query?
"Paperback Writer" <Paperba...@discussions.microsoft.com> wrote in
message news:C86F3C51-E3DA-4537...@microsoft.com...
Gina Whipp
He cannot show what cannot be done without some coding.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
news:eTo8uuID...@TK2MSFTNGP02.phx.gbl...
BruceM
"Gina Whipp" <NotInt...@InViruses.com> wrote in message
news:u4rgiFJD...@TK2MSFTNGP03.phx.gbl...
Gina Whipp
that work ;)
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
news:u%23Pw1iJD...@TK2MSFTNGP05.phx.gbl...
John W. Vinson
wrote:
>You mean an empty recordset can't return a result?!?
"Nihil ex nihil adveniat".
--
John W. Vinson [MVP]
Arvin Meyer [MVP]
First create a label that says something like "No records to display" Then
write a piece of code that checks the subform's recordset.recordcount for
records. Something like:
Dim db As DAO.Database
Dim rst AsDAO. Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Your Subform Recordsource")
If rst.RecordCount = 0 Then
Me.NameOfSubFormControl.Height = 0"
End If
Now what happens is that the label below the subform control gets exposed if
there are no records.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Paperback Writer" <Paperba...@discussions.microsoft.com> wrote in
message news:E92082BB-8E99-4B51...@microsoft.com...
Arvin Meyer [MVP]
> Again, my original question wasn't vague or misleading in any way. It was
> direct, and very precise.
Hmm. Your original question was:
"Any easy easy way to make a Select Query say that there are no results when
the query finds no data that matches the criteria?"
That does not mention forms or reports or the subforms you eventually asked
about.
> And, I did figure out how to make the query work.
Now that is a claim, that we are all waiting to see.
KenSheridan via AccessMonster.com
SELECT SomeColumn, SomeOtherColumn
FROM SomeTable
WHERE SomeColumn = "Some Value"
UNION ALL
SELECT DISTINCT "No data", NULL
FROM SomeTable
WHERE NOT EXISTS
(SELECT *
FROM SomeTable
WHERE SomeColumn = "Some Value");
Or if SomeTable can be empty create a table Dummy with column DummyID of the
same data type as the key of SomeTable and insert one row, then:
SELECT SomeColumn, SomeOtherColumn
FROM SomeTable
WHERE SomeColumn = "Some Value"
UNION ALL
SELECT DISTINCT "No data", NULL
FROM SomeTable RIGHT JOIN Dummy
ON SomeTable.SomeID = Dummy.DummyID
WHERE NOT EXISTS
(SELECT *
FROM SomeTable
WHERE SomeColumn = "Some Value");
Not that anyone in their right mind would.
Ken Sheridan
Stafford, England
Arvin Meyer [MVP] wrote:
>> Again, my original question wasn't vague or misleading in any way. It was
>> direct, and very precise.
>
>Hmm. Your original question was:
>
>"Any easy easy way to make a Select Query say that there are no results when
>the query finds no data that matches the criteria?"
>
>That does not mention forms or reports or the subforms you eventually asked
>about.
>
>> And, I did figure out how to make the query work.
>
>Now that is a claim, that we are all waiting to see.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200907/1
BruceM
rudimentary is my knowledge of Latin.
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:96hk651jn3gag026u...@4ax.com...
BruceM
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 ;-)
"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:99a92d58ad1c0@uwe...
KenSheridan via AccessMonster.com
person singular present active subjunctive form of the Latin verb advenire,
which means 'to come to', 'to accrue' or similar. The subjunctive mood gives
a 'let' or 'may' meaning to verbs. It’s the curse of having had a British
classical education that these things stick with you for life!
Ken Sheridan
Stafford, England
BruceM wrote:
>Nothing comes from nothing. My guess was pretty close considering how
>rudimentary is my knowledge of Latin.
>
>>>You mean an empty recordset can't return a result?!?
>>
>> "Nihil ex nihil adveniat".
--
KenSheridan via AccessMonster.com
any sort of query solution. But I'm not prejudiced; if he can show me a
better mousetrap I'll beat a path to his door!
Ken Sheridan
Stafford, England
BruceM wrote:
>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 ;-)
>
>> Out of purely academic interest this is how it can be done:
>>
>[quoted text clipped - 47 lines]
BruceM
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.
"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:99b58a0afa382@uwe...
Paperback Writer
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
SELECT *
FROM [Table 1]
UNION SELECT *
FROM [Table 2];
BruceM
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.
"Paperback Writer" <Paperba...@discussions.microsoft.com> wrote in
message news:32ECC44B-15A8-42B4...@microsoft.com...
Bob Quintal
<Paperba...@discussions.microsoft.com> wrote in
news:DE68151A-8B45-4DEC...@microsoft.com:
> 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.
--
Bob Quintal
PA is y I've altered my email address.
BruceM
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.
"Bob Quintal" <rqui...@sPAmpatico.ca> wrote in message
news:Xns9C5962A57...@69.16.185.252...
KenSheridan via AccessMonster.com
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
--
James A. Fortune
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
MPAP...@FortuneJames.com
Clichés are the lingua franca of the political world.
KenSheridan via AccessMonster.com
James". That is meant to be a compliment BTW.
Ken Sheridan
Stafford, England
James A. Fortune wrote:
>> 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
>[quoted text clipped - 30 lines]
>> 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
>MPAP...@FortuneJames.com
>
>Clichés are the lingua franca of the political world.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200908/1
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
MPAP...@FortuneJames.com
Dim I As Integer
Let I = 5
KenSheridan via AccessMonster.com
Irish!
Regards,
Ken
--
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
MPAP...@FortuneJames.com