Problem with zeros

[jtm013]

Hello,

I know similar threads are out there but I was not able to solve my problem through them.
I have a simple query which is counting the number of results in a search.
I need it to display 0 when there are zero results instead of returning empty.
How can I accomplish this?
My SQL is:

SELECT Count(tblDataPrime.recordID) AS CountOfrecordIDFROM tblDataPrime
WHERE ((([toolNextCalDate]<Date())=-1) AND ((tblDataPrime.toolActive)=-1))
GROUP BY tblDataPrime.toolLocat
HAVING (((tblDataPrime.toolLocat)<>"Scrap"));

I also tried:

SELECT NZ(Count(tblDataPrime.recordID),0) AS CountOfrecordIDFROM tblDataPrime
WHERE ((([toolNextCalDate]<Date())=-1) AND ((tblDataPrime.toolActive)=-1))
GROUP BY tblDataPrime.toolLocat
HAVING (((tblDataPrime.toolLocat)<>"Scrap"));

But this did not work either.



Thank you for your assistance.

[June7]

If toolLocat does not have data to count then will not produce a record. If you want all toolLocat values to be listed regardless then need to include a master list of all toolLocat values in the query.

[jtm013]

I don't need to display all values. I just want the query to show a zero when there are no values.

[June7]

Why would there be a record if there is no value?

Show example raw data and example desired output.

[jtm013]

Why would there be a record if there is no value?

Show example raw data and example desired output.

This query simply does a count of results meeting the criteria.

So if there are 5 records meeting the criteria the query produces the number "5" which is shown in the counter box on my form.

If there are no results it needs to display the number "0" in the counter box on my form.
This is not producing information about the records, simply the number of records which meet the criteria.
All that this query shows is the number of records meeting the criteria, however, 0 records meeting the criteria is still useful to the user to see.

Example 1:

Raw data:
Record Id: 1 ToolnextCalDate: 04/30/2015
Record Id: 2 ToolnextCalDate: 07/31/2015

Query produces: 1

Example 2:

Raw data:
Record Id: 1 ToolnextCalDate: 05/31/2015
Record Id: 2 ToolnextCalDate: 07/31/2015

Query produces: 0

[June7]

Where are you using that query?

Do you want to count records displayed on form that are "Scrap"?

Expression in textbox in form footer: Sum(IIf([toolLocat]="Scrap",1,0))

[jtm013]

Where are you using that query?

Do you want to count records displayed on form that are "Scrap"?

Expression in textbox in form footer: Sum(IIf([toolLocat]="Scrap",1,0))

The dashboard of my database is a form with 3 subforms. Each subform is continuous and acts as a table to show the user which records are existing in which state. (This is a tool database) So one subform is tools due for calibration this month.
Another is tools due for calibration next month. The final subform is tools overdue. Each sub has a title "Tools due this month" "Overdue tools", etc. Beside this title is a box which displays the number of tools (records) which are overdue, due this month, etc.

This query's sole function is to put that number in that box. This specific query is the one for overdue tools. Overdue tools is unique as of the three subs it is the only one which has the possibility of showing zero records. I know how to count the overdue tools, I am excluding scrap records. The query functions as desired as far as going through my raw data, it simply does not show the number zero. If there are 5 overdue tools, it will display "5" if 4 it displays "4", 1 "1" and so on. But when there are no overdue tools it displays as null. rather than showing a "0".

The method for displaying the number is a textbox the data source for which is this query.

[June7]

The textbox ControlSource is an SQL statement? When did that become possible?

I have used domain aggregate functions for this.

[jtm013]

The textbox ControlSource is an SQL statement? When did that become possible?

I have used domain aggregate functions for this.

No the control source is this query. I posted the sql of the query. I usually use the design view to construct queries, but from what I have read in other threads in order to show a zero when counting things in a query you have to use the sql...which I am unfamiliar with and could not get the syntax right.

I don't understand why it is so difficult to show a zero when using a query to count. zero is a number it should be displayed.

To review- I have a form, (the dashboard) there is a subform which shows overdue tools. this sub has a title which says "Overdue Tools" beside it is a subform whose control source is this query and on that subform is a textbox the control source of which is "countofrecordID". Functionally, to the user, they look at the screen and see a nice simple label which reads "Overdue Tools - 0 overdue tools" Where the "0" is however many tools are overdue at that time, it may be 0 it may be 50.

The problem is that if it is actually zero overdue tools the user sees "Overdue Tools - _ overdue tools".

[June7]

Reference to query object or an SQL statement - same difference - since when can a ControlSource be a query (or SQL statement)?

If you want to provide db for analysis, follow instructions at bottom of my post.

[jtm013]

What do you mean how can it be a control source I click the drop down arrow and it is on the list.

Why does it matter how it is associated to the text box. The problem is the query will not display zero. It just doesn't display anything.

[June7]

I am lost. I see only fields as available for textbox ControlSource, not query objects.

I would use DSum() or DCount() domain aggregate function.

[jtm013]

Yes but as I mentioned above this text box is on a subform. The subform's control source is the query. The text box's source is the field "countofrecordid".

That said why does this affect the query not showing a zero. If I open the query direct and run it when a zero should be shown it shows nothing. So it is not a problem with the controlsource.

[jtm013]

Okay I have attached a quick and dirty version.

When you open it select the guest login and input password: "password" (no quotes)
this loads the dashboard. I have dumbed it down a bit to get the file size down so only the "Late Tools" now shows the whole "Late tools X number of tools are overdue" type of message. The number, which I changed the formatting so it is obvious it is separate from the rest of the labels, is generated by the query.

The query is named querycountoverdue. This query has only one visible field- countofrecordid, the value in this field is the number shown on the dashboard form.
When there are no overdue tools it displays nothing nada zilch all i need is for it to display a zero. (Yes I understand no records will be shown in the window I am okay with that.)

For testing purposes to change the due date on the example tool just click the icon for the tool "0000" this will open it's information page the due date is calculated from the last calibration date and the interval so just alternate the last calibrated date accordingly and this will move the record from being overdue to not overdue and you will see the problem.

Again, all I need to do is get a field in a query to show the number 0 instead of showing nothing at all.

New Compressed (zipped) Folder.zip

[June7]

I am looking at frmCountOverdue. It has one textbox bound to CountOfRecordID.

This expression in textbox should work: =Nz([CountOfrecordID],0)
However, I get error "There is an error compiling this function. The Visual Basic module contains a syntax error. Check the code, and then recompile it."
Your VBA has procedures with API calls that won't run on my 64-bit computer. I simply deleted the two procedures and then Access compiles properly and the expression is accepted.

If there is a chance this db will run on 64-bit machines, need to research PtrSafe parameter in API declarations.