show a 0 if there is nothing to count in a query

[megatronixs]

Hi all,

I'm using a query to count cases on a day. It could hapen that on that day there are no cases or the day is not yet (like running the query today and getting a 0 for tomorrow Friday)


I'm using this SQL:

SELECT Count(tbl_Reviews_case_list.[Case Status]) As [On Hold Tuesday] 
FROM tbl_Reviews_case_list 
GROUP BY tbl_Reviews_case_list.[Case Status] 
HAVING (((tbl_Reviews_case_list.[Case Status])="On hold") And ((Count(tbl_Reviews_case_list.[On Hold (date)]))<#8/18/2015#));

The date is now hard coded, but, this will be changed later.

Greetings.

[ranman256]

what if you put it in a field and the count converting to zero

txtBox = NZ(dcount("*","qsCountQry"))

[megatronixs]

Hi Ranman,

I'm using it actually only in a query to create the SQL to use it in Excel vba
it is a long story :-(
I have a report that uses over 5000 cells full of formulas and wanted to pull the data straight from access database.

Greetings.

[ranman256]

then import the data, then get the line count.

[megatronixs]

Hi Ranman,

My knowledge is not the greatest and it is kind of trying to do magic. The connections sometimes fails and most of the time all goes very slow (I have only so far 5 queries working, so it should be not that slow)
I tried to make the report in access, but then, it is hard to get the same layout as I could in excel and it easier to place thing on a grid than the report layout from access.

Or maybe I'm just wrong and access could do a better job?

Greetings.

[June7]

If there is no data then there is nothing to count, can't even show 0.

This would require a dataset of all possible date/status pairs then join that dataset to the primary raw dataset - join type "Show all records from date/status dataset and only those from raw dataset that match".

[megatronixs]

Hi June7,
The second part I don't get. You mean to create the complete report I would need a lot of data connected let's say in a query and then query that data?

[June7]

If I understand your question - yes, two queries.

Creating the dataset of all possible date/status pairs might be possible with a Cartesian query. A Cartesian query is a query without a JOIN clause - every record of a table will be associated with every record of another table. This would require a data source that has a record for every date and a data source of all status values.

SELECT DISTINCT [date field], Status FROM [date field table], [status table];

If you don't have a table that has at least one record for every date, this won't work.

[megatronixs]

Hi June7,
sounds kind of daunting, no clue if I will be up to the job.
I will maybe first get the data into excel book and use there the sql, maybe the connection will be much faster. I still need to figure out how it will work and need to look on many different ways. It will take me still some days before I have reasearched everything.

Greetings.

[megatronixs]

Hi all,

I solved by using vba to first get all the data into the workbook, and then with vba I run a autofilter with criteria (one criteria that takes date from a cell).
Then there I use the "Application.WorksheetFunction.Subtotal" and this one give me the count I need.

It works really fast and I only need to pull once the whole data in to get it done :-)

Greetings.

[megatronixs]

I forgot to say that it also gives me '0' if there is nothing to count :-)