Require 0 if none found via query

[fishface616]

Hi, I have a table ACT_TYPE_SC that I am trying to query, it has many possibilities but I want to check for certain criteria ie the word "Delay" and if none are found return a zero as this is used later on. I have tried Nz, Isnull but can get no joy only a blank space, please help the complete novice !! thanks
Attempts;

Field:Nz([SA_ACT_TYPE.ACT_TYPE_SC],"0")

or

Field:Count(*(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0)))

or

Field:IIf(IsNull([SA_ACT_TYPE].[ACT_TYPE_SC]),0)

all with Criteria: Like "Delay*"

[NeoPa]

Try :

Field: Nz(Count(*),0)

Of course, this depends on exactly how your query is designed. It would be a good idea to include the full SQL of your query so we can get a better understanding of what you're actually asking. Choose View | SQL in the QueryDef to see and capture the SQL to post.

[fishface616]

Hi, thanks for quick response, attached is the SQL (Mod - Removed attachment and included the code properly in the post). the reference to ISO Workday in it is another query regarding dates. Thanks in advance as I have spent days on this. :-(

SELECT   IIf([Actual_Days]<31,"OnTime","OOPS") AS Result
       , SA_INC_SERIOUS.INC_SERIOUS_SC
       , SA_ITEM.ITEM_SC
       , SA_INCIDENT.DATE_LOGGED
       , SA_INCIDENT.INC_CLOSE_DATE
       , ISO_WorkdayDiff([INC_CLOSE_DATE],[DATE_LOGGED],True) AS Actual_Days
       , [INC_CLOSE_DATE]-[DATE_LOGGED] AS Days
       , Year([INC_CLOSE_DATE]) AS [Year]
       , Month([INC_CLOSE_DATE]) AS Expr1
       , SA_INCIDENT.INCIDENT_REF
       , Count(*(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0))) AS Expr2
FROM     SA_ACT_TYPE
         INNER JOIN
         ((SA_ITEM 
         INNER JOIN
         (SA_INCIDENT
         INNER JOIN
         SA_INC_SERIOUS
  ON     SA_INCIDENT.INC_SERIOUS_ID = SA_INC_SERIOUS.INC_SERIOUS_ID)
  ON     SA_ITEM.ITEM_ID = SA_INCIDENT.ITEM_ID)
         INNER JOIN
         SA_ACT_REG
  ON     SA_INCIDENT.INCIDENT_ID = SA_ACT_REG.INCIDENT_ID)
  ON     SA_ACT_TYPE.ACT_TYPE_SC = SA_ACT_REG.ACT_TYPE_SC
GROUP BY SA_INC_SERIOUS.INC_SERIOUS_SC
       , SA_ITEM.ITEM_SC
       , SA_INCIDENT.DATE_LOGGED
       , SA_INCIDENT.INC_CLOSE_DATE
       , ISO_WorkdayDiff([INC_CLOSE_DATE],[DATE_LOGGED],True)
       , [INC_CLOSE_DATE]-[DATE_LOGGED]
       , Year([INC_CLOSE_DATE])
       , Month([INC_CLOSE_DATE])
       , SA_INCIDENT.INCIDENT_REF
       , SA_INCIDENT.ASS_SVD_ID
HAVING   (((SA_INC_SERIOUS.INC_SERIOUS_SC)="ITORD 30 DAY")
   AND   ((Year([INC_CLOSE_DATE]))=Year(Date()))
   AND   ((Month([INC_CLOSE_DATE]))=Month(Date()))
   AND   ((Count(*(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0)))) Like "Delay*")
   AND   ((SA_INCIDENT.ASS_SVD_ID)=495))
ORDER BY SA_INCIDENT.INC_CLOSE_DATE

[NeoPa]

FishFace:
The reference to ISO Workday in it is another query regarding dates.

??? The only thing I see remotely close to "ISO Workday" in your code doesn't look anything like a query reference. ISO_WorkdayDiff([INC_CLOSE_DATE],[DATE_LOGGED],True) appears to be a function of some sort.

[fishface616]

It is a holiday/weekend VB script.. It is used later on when I combine two queries which is part of the reason I need to show zero if nothing found... Please ignore it if possible.. thanks

[zmbd]

Line 11:

Count(*(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0))) AS Expr2 

and again in line 38:

((Count(*(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0)))) Like "Delay*")

Remove the "*". I'm surprised the engine took that construct at all... infact, when I went to verify this, my test db puked a syntax error.

SO, lets take a quick look at the Count() in SQL:
count(*) says count everything and return the number of records.
count([SomeFieldName]) says count everything except null values within the givien field. So, it would seem to me then that: Count(*) would be equal to Count(Nz([SA_ACT_TYPE.ACT_TYPE_SC],0)).
Which, again in my test database, is exactly what happens.

Even with removing the "*" from the code, I'd be surprised to see anything except a "1" as the count returned in line 11.

SO, line 38 will have the same issue as line 11.

as for the remainder of the SQL, I don't have your tables nor dataset in front of me nor have you really defined your issue; thus, I am unable to fathom much more of your SQL... perhaps Rabbit or Neopa will have better understanding.

> Before Posting (VBA or SQL) Code.
> How to ask "good" questions
> POSTING_GUIDELINES: Please Read Carefully Before Posting to a Forum.

[NeoPa]

Try :

Sum(IIf(SA_ACT_TYPE.ACT_TYPE_SC Like 'Delay*',1,0))

or :

Count(IIf(SA_ACT_TYPE.ACT_TYPE_SC Like 'Delay*',1,Null))

[zmbd]

duh... feeling stupid now... I use that same type of code for the "sub total" and "grand total" in the reports.

These little new-borns are so cute; however, I think that they are draining my intelligence out of my mind along with that bottle every 3 hours!

[NeoPa]

Z:
These little new-borns are so cute; however, I think ...

How many new ones have you got??!? You talk as if they came in a batch! Crated babies just doesn't sound right :-D

[zmbd]

Still that "*" is causing a glitch.

--
Only the one new one... but with the twins hitting the 3yr mark... it seems like alot more some days

[fishface616]

Gents, thanks for your responses, I still get a blank row, however if enable Totals and use the drop down box I get the option to put a zero in, but it never stays there. so when I run the query it go back to being blank... Almost hair pulling out time !!

[zmbd]

Then none of your data is matching queries within the realationships you've defined.

Thus the need for your tables and a small amount of sample data.

The data doesn't have to be "real" just representitive... for example it is a common usage for "John M Doe", "Jane H. Doe" amd "John Q. Public" as stand-ins for people names. 555-1212, 555-1234, etc for telephone. [phone] and so-on for SSN,

[NeoPa]

Your FROM clause is built up using many INNER JOINs. Depending on your data this is likely to restrict the amount of records that result from this query quite substantially. Your HAVING clause further restricts what data can show. You need to look at these issues before wondering why the result isn't zero. There is no zero result because there are clearly no output records for the result to be included in. The query specification is so restrictive that you have no matching/valid data to show at all.

[fishface616]

Sorted... it was the way i had linked the Tables, once i removed the not required and checked the Joins it worked :-) Thanks for all the help !!!