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

[Paperback Writer]

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?

Thanks!!!

 

[Arvin Meyer [MVP]]

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.

 

[Tom van Stiphout]

On Thu, 23 Jul 2009 09:34:07 -0700, Paperback Writer

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]

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.

 

[Jim Franklin]

You could use code in the relevant form event to check if there are records
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]

Actually, I think my question was very clear. I'm sorry if it didn't fit
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]

Actually, I think my question was very clear. I'm sorry if it didn't fit
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!!!

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

 

[Tom van Stiphout]

On Thu, 23 Jul 2009 10:04:01 -0700, Paperback Writer

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]

You know what they say, "Like attracts like." You could also say, "Flame
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]

I for one would like to know how you solved your problem. I may have misread
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]

I join John in wondering how you did this. The only way I can see for a
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.

 

[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
(e-mail address removed)

 

[BruceM]

Here it is almost twenty-four hours later. When are you going to show how
you did this in a query?

 

[Gina Whipp]

Bruce,

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]

You mean an empty recordset can't return a result?!?

 

[Gina Whipp]

Oh Bruce... I'm sorry, I hope you didn't waste alot time trying to make
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

 

[John W. Vinson]

You mean an empty recordset can't return a result?!?

"Nihil ex nihil adveniat".

 

[Arvin Meyer [MVP]]

In a form/subform it is relatively easy.

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

 

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

 

[BruceM]

Nothing comes from nothing. My guess was pretty close considering how
rudimentary is my knowledge of Latin.