Query returning Blank

[Stemdriller]

If a query comes back with a blank, can you use tell the query to display a Zero instead??

Thanks

 

[John Big Booty]

If you are using your query to populate a form you can test;

Me.RecordSet.RecordCount = 0

In the form's On Load event.

 

[Stemdriller]

If you are using your query to populate a form you can test;

Me.RecordSet.RecordCount = 0

In the form's On Load event.

My aim is to count the data in various queries and then transfer the data into a Pie Chart. Once there is an entry the query works ok, but if there is no entry then the whole query returns blank cells.

 

[DCrake]

You would need to do a DCount on each query to decide if you need to pass it to the pie chart.

 

[Stemdriller]

Thanks for responding

The table the query is querying may not yet have any entries, so quite rightly the query is not returning any records.

I am trying to get the query to display a 0 if there are no records. If the query comes back with a zero, I am then hoping to be able to Count this Zero in another query.

In the query i have tried IIF(IsNull,0)

One Master Query is counting a field in 4 minor queries which I am then hoping to transfer into a chart.

 

[jesse]

If you want to know if a query returns 0 records you can use something like

EXISTS(<your SELECT query here>) --> returns FALSE if the count=0

or something like

SELECT COUNT(*) FROM (<your select query here>) --> returns 0 if the result of your query is an empty set

hope it helps.

 

[Stemdriller]

Jesse

Thanks for your input

I don't really understand what your saying. If the result of the query is an empty set or blank, just would like it to return a Zero so another MasterQuery can take the value and add it to it's final count that gets used for a Pie Chart.

It would appear that if one of the 4 sub queries returns an empty set then the MasterQuery will also return an empty set, even if the other sub queries are populated.

I am beginning to wonder now if i am barking up the wrong tree!

Gareth

 

[Stemdriller]

Jesse

Thanks for your input

I don't really understand what your saying. If the result of the query is an empty set or blank, just would like it to return a Zero so another MasterQuery can take the value and add it to it's final count that gets used for a Pie Chart.

It would appear that if one of the 4 sub queries returns an empty set then the MasterQuery will also return an empty set, even if the other sub queries are populated.

I am beginning to wonder now if i am barking up the wrong tree!

Gareth

I have a attached a test database, Can someone try and get 'Query1' to return a value of 0

 

[vbaInet]

The issue is, like jesse mentioned, if the table is empty it will return 0. However, if you apply a condition and there's no matching record, it will not return 0, it will come up as blank.

This was why DCrake mentioned using the DCount() function.

 

[Stemdriller]

The issue is, like jesse mentioned, if the table is empty it will return 0. However, if you apply a condition and there's no matching record, it will not return 0, it will come up as blank.

This was why DCrake mentioned using the DCount() function.

Would you put the DCount in the criteria of the query?

 

[vbaInet]

You DCount on the query.

DCount("*", "queryName")

 

[Stemdriller]

You DCount on the query.

DCount("*", "queryName")

I just cannot get my head around this.

Could you not update the test database that I have attached so that I can see what everyone is getting at.

A really do appreciate your time and expertise, vbaInet

Regards
Gareth

 

[vbaInet]

It would be hard to put in your database because I don't know how you're translating this into the main query.

So maybe show us a screenshot of how the queries are being used in the main query.

 

[Stemdriller]

OK, Screenshot attached

Hope you can help

 

[jesse]

There is no way for a query to return either a number of rows (with more than one field) or if there are no rows then return a single zero. Queries return rows, not values. If there are no resulting rows the query returns an empty set.

When I look at your query:

SELECT Table1.ID, Count(Table1.Test1) AS CountOfTest1
FROM Table1
GROUP BY Table1.ID, Table1.Test1
HAVING (((Table1.Test1)=2))

I suspect you maybe always want a grand total of COUNT(table1.test1)? Why not just use a SUM() for that?

 

[vbaInet]

Maybe there is a way. Look at qryCount. To use this kind of query you will have to know some sql syntax and for dynamic criteria, it must be built in vba.

I don't know how this would affect your chart though.

 

[Stemdriller]

Hi Everyone

I really struggling with this.

If one of you kind soles would open up the DB i have attached.

There are 2 querys called qryCSTRQuality and qryCSTRQualityCount.

I need to get qryCSTRQualityCount to Count the individual inputs.

eg How many users have put Good or Fair so I can then use the results to for Charts.

At the moment it just counts the total records.

So qryCSTRQualityCount should result in Fair = 1 and Good = 2

Please Please anyone