Null needs to be shown as 0 regardless

[ablatnik via AccessMonster.com]

I have a Work Order database. To date everything works fine until now. I
have 9 Departments and on any given day, they have outstanding work requests
in the system. Until now of course. The department "UEM" they are heating
and air conditioning; anyhow all their work request need to closed prior to
close of the business day.

As long as there is an open or Active work order, the query works. Zero's
show up in the correct fields so long as in at there is a number greater than
zero.

With all the Work Orders closed, I get a blank UEM and a blank UEM report.
I've added the =Count to the UEM report which gives me a zero on my Totals
Report. (The Totals Report is a collection of about 150 queries/reports on
one page).

=Count is not acceptable, it will not give me the sum of all outstanding
orders across the months.

What I need is for the query to return a zero (with counting). The report on
this query will show zero's, and with that the Totals Report, will show a
zero where it needs to be. etc...

Here is the SQL code:

TRANSFORM Nz(Count([Total Active Work Orders By Month for the Year].Status),0)
AS CountOfStatus
SELECT [Total Active Work Orders By Month for the Year].Department, Count(
[Total Active Work Orders By Month for the Year].Status) AS [Total Of Status]
FROM [Total Active Work Orders By Month for the Year]
WHERE ((([Total Active Work Orders By Month for the Year].Department)="UEM"))
GROUP BY [Total Active Work Orders By Month for the Year].Department
PIVOT Format([Date Opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun",
"Jul","Aug","Sep","Oct","Nov","Dec");


If someone can help me on this one, much thanks. Even if I have to chance
the other 150 query/ reports.

 

[John Spencer]

Is Total Active Work Orders By Month a query? If so, you might post the
SQL for that one also. If that query is returning no records, then this
query will have no "records" to work with and therefore will return nothing.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ablatnik via AccessMonster.com]

Here is the SQL statement for Total Active Work Orders By Month ror the Year.
When I look at the numbers from the Totals report, UEM only has 4 work orders
open. For testing purposes I closed them and that is when I get the #error.
I know there are no numbers to work with. So from this point, what I would
need is for null to display a 0 regardless if all work orders are closed.

SELECT Department.Department, [Work Orders].[Date Opened], Status.Status
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.
Department_ID = [Work Orders].Department) ON Status.Status_ID = [Work Orders].
Status
WHERE (((Status.Status)="Active" Or (Status.Status)="Pending" Or (Status.
Status)="On Hold" Or (Status.Status)="Active - Damaged"));

Is Total Active Work Orders By Month a query? If so, you might post the
SQL for that one also. If that query is returning no records, then this
query will have no "records" to work with and therefore will return nothing.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a Work Order database. To date everything works fine until now. I
have 9 Departments and on any given day, they have outstanding work requests

[quoted text clipped - 32 lines]

If someone can help me on this one, much thanks. Even if I have to chance
the other 150 query/ reports.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't you have a [Work Orders].[Date Closed]? You could use that to
select both open & closed work orders:

WHERE ([Work Orders].[Date Closed] IS NULL AND Status.Status IN
("Active","Pending","On Hold","Active - Damaged"))
OR ([Work Orders].[Date Closed] IS NOT NULL AND Status.Status =
"Closed")

Use the IN () operator whenever you have multiple ORs for the same
column - it works the same and is easier to read/understand.

When [Date Closed] is null that means the order is still open; when it
is NOT null that means it was closed.


Then when the cross-tab query runs it should have a zero if you change
the TRANSFORM clause to this (untested):

TRANSFORM SUM(IIf([Total Active Work Orders By Month for the
Year].Status = "Closed",0,1)) AS CountOfStatus
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSX0cc4echKqOuFEgEQJUHQCfcusVwGhZg5p8rZHoi2+nisgUgGkAn1oB
BXkNoNFI1w1v2/mBt7KeKdlD
=YJj1
-----END PGP SIGNATURE-----

Here is the SQL statement for Total Active Work Orders By Month ror the Year.
When I look at the numbers from the Totals report, UEM only has 4 work orders
open. For testing purposes I closed them and that is when I get the #error.
I know there are no numbers to work with. So from this point, what I would
need is for null to display a 0 regardless if all work orders are closed.

SELECT Department.Department, [Work Orders].[Date Opened], Status.Status
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.
Department_ID = [Work Orders].Department) ON Status.Status_ID = [Work Orders].
Status
WHERE (((Status.Status)="Active" Or (Status.Status)="Pending" Or (Status.
Status)="On Hold" Or (Status.Status)="Active - Damaged"));

Is Total Active Work Orders By Month a query? If so, you might post the
SQL for that one also. If that query is returning no records, then this
query will have no "records" to work with and therefore will return nothing.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a Work Order database. To date everything works fine until now. I
have 9 Departments and on any given day, they have outstanding work requests

[quoted text clipped - 32 lines]

If someone can help me on this one, much thanks. Even if I have to chance
the other 150 query/ reports.

 

[ablatnik via AccessMonster.com]

Truth be told, the University does not use a [Date Closed]. I asked the boss
this prior to building this. His response, "When it is closed, it is filed
away." Now that you mention it though, I could reverse this statement
around. Query the Status field to report on total closed work orders minus
total work orders.

SELECT Department.Department, [Work Orders].[Date Opened], Status.Status
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.
Department_ID = [Work Orders].Department) ON Status.Status_ID = [Work Orders].
Status
WHERE (((Status.Status)="Active" Or (Status.Status)="Pending" Or (Status.
Status)="On Hold" Or (Status.Status)="Active - Damaged"));

Is Total Active Work Orders By Month a query? If so, you might post the
SQL for that one also. If that query is returning no records, then this

[quoted text clipped - 12 lines]

 

[ablatnik via AccessMonster.com]

A little guidance please. I am going to add a [Date Closed] field to the
[Work Order] table. I know little about VB but I'm sure it will be an event
somehow. What I would like is the [Date Closed] to populate automatically
with the current date (updatable in case it needs to be changed), when the
[Status] field is marked Closed. But at the same time, can this [Date Closed]
field be wipped clean if there is any thing else in the [Status] field.

Truth be told, the University does not use a [Date Closed]. I asked the boss
this prior to building this. His response, "When it is closed, it is filed
away." Now that you mention it though, I could reverse this statement
around. Query the Status field to report on total closed work orders minus
total work orders.

SELECT Department.Department, [Work Orders].[Date Opened], Status.Status
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.

[quoted text clipped - 8 lines]

 

[John Spencer (MVP)]

If you are entering data through a form, you can use VBA code in the BEFORE
UPDATE event of the form

Assuming two controls named txtStatus and txtDateClosed the VBA would look
like the following. (Warning: untested code)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtStatus = "Closed" then
Me.txtDateClosed = Date()
Else
Me.txtDateClosed = Null
End IF
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

A little guidance please. I am going to add a [Date Closed] field to the
[Work Order] table. I know little about VB but I'm sure it will be an event
somehow. What I would like is the [Date Closed] to populate automatically
with the current date (updatable in case it needs to be changed), when the
[Status] field is marked Closed. But at the same time, can this [Date Closed]
field be wipped clean if there is any thing else in the [Status] field.

Truth be told, the University does not use a [Date Closed]. I asked the boss
this prior to building this. His response, "When it is closed, it is filed
away." Now that you mention it though, I could reverse this statement
around. Query the Status field to report on total closed work orders minus
total work orders.

SELECT Department.Department, [Work Orders].[Date Opened], Status.Status
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.

[quoted text clipped - 8 lines]

If someone can help me on this one, much thanks. Even if I have to chance
the other 150 query/ reports.