wrong number of arguements

[jamiec1]

I am getting this error when i run the following code


SELECT ProblemCategory.ProblemCategory, IIf(IsNull(Count(JobRecords.JobID),0, Count(JobRecords.JobID) )) AS JobsInCat
FROM CustomerContracts INNER JOIN (CoveredSites INNER JOIN (ProblemCategory right JOIN JobRecords ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY ProblemCategory.ProblemCategory;

its the
IIf(IsNull(Count(JobRecords.JobID),0, Count(JobRecords.JobID) ))

bit which is causing the probs but not sure what is wrong with it

 

[Steve101]

Jamie,

Try replacing:

IIf(IsNull(Count(JobRecords.JobID),0, Count(JobRecords.JobID) ))

with:

Count(IIf(IsNull(JobRecords.JobID),0, JobRecords.JobID))

This will Count up only those JobIds which do not have a Null value.

I hope that this does the trick,
Cheers,
Steve

 

[blom0344]

Hello Jamie,

You better follow the original thread you started, instead of addressing the same issue again and again in new threads, cause it's difficult to keep track of your progression.

try:

IIf(IsNull(Count[JobRecords.JobID])),0, Count[JobRecords.JobID] ) *** you got the bracketing wrong***

 

[Steve101]

Blom,

(a) Keep the Count on the OUTSIDE of the expression; its the subject of the aggregate query

(b) Count your opening and closing brackets; they dont balance.

Cheers,
Steve

 

[blom0344]

Hello Steve,

Sorry about the missing bracket.

I was answering to the original thread jamie started (this tread is the third concerning his issue)

His original question was:

*********************************************************

need to display the count of jobs logged by month but include zeros where no jobs have been logged in a mth

SELECT format(DateTimeCreated, "yyyy/mm&quot As Mnth , count(JobRecords.DateTimeCreated) AS Tot
FROM JobRecords
GROUP BY format(DateTimeCreated, "yyyy/mm"

this returns
2002/04 20
2002/05 34

but i need to return

2001/01 0
2001/02 0
2001/03 0
2001/04 20
2001/05 34
etc up to current mth

***********************************************************

So , what he basically want is:

Return of every month , even if conversing of dates does not produce every month-occurence AND creating the output of a zero instead of "empty" in his query output.

My suggestion was a pre-query on his original table and combine the output in a second query with an additional table with all possible month-occurences.

So, the count should really be inside the expression to get the right validation if it results in a blank, to fill in the '0'.

Since this issue is now spread over 3 threads
("month report to include zero's",
"possibly an impossible problem",
"wrong number of arguements&quot
, I'll leave it alone. You might have an alternative to help jamie with this one.

 

[jamiec1]

sorry for the spread guys im new here .. will keep it together in future..

 

[jamiec1]

This was a different query though .. just trying a similar thing as the other one

 

[rajeessh]

SELECT ProblemCategory.ProblemCategory,
IIf(IsNull(Count(JobRecords.JobID)),0,Count(JobRecords.JobID) ) AS JobsInCat
FROM CustomerContracts INNER JOIN (CoveredSites INNER JOIN (ProblemCategory right JOIN JobRecords ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY ProblemCategory.ProblemCategory;

 

[Steve101]

Guys,

Dont agree with the IIf(IsNull(Count..... approach; reckon the Count bit needs to be on the outside. Jamie, please try these things out in your app and let us know what works for you,

Steve

 

[jamiec1]

SELECT ProblemCategory.ProblemCategory,
IIf(IsNull(Count(JobRecords.JobID)),0,Count(JobRecords.JobID) ) AS JobsInCat
FROM CustomerContracts INNER JOIN (CoveredSites INNER JOIN (ProblemCategory right JOIN JobRecords ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY ProblemCategory.ProblemCategory;


This code runs but does not return the results i want .. it returns same result as my my orig query in which i had
Count(JobRecords.JobID) instead of
IIf(IsNull(Count(JobRecords.JobID)),0,Count(JobRecords.JobID))

basically i need to have all of the records from ProblemCategory.ProblemCategory with zeros where approp in the report .. what i get is

ProblemCategory JobsInCat
Desktop 22
Email 16
Fax Server 2
Laptop 3
MS SQL 1
Networking 3
Phones 2
Printer 4
Router 4
Server 5
Windows 98 2
Windows NT 30

there are other cats but cant get them to show zeros
also need to do all in one statement because i have to pass in variable CustomerID from an asp page.

 

[Steve101]

Jamie, OK I understand the problem.

(a) I think that your Join types may be wrong in the query. You may need to ensure that the join arrow points OUT of the ProblemCategory table to ensure that all ProblemCategories are always accomodated. Double click on the join line and change as appropriate. Other relationships involved in the query may need to be reviewed as well.

(b) The other thing that is possibly causing the problem is the WHERE clause. Change this to:

WHERE (CustomerContracts.CustomerID=1 OR
CustomerContracts.CustomerID IS NULL)

ie. add the IsNull check. What is happening is that when the joins are constructed, those problem categories without customers, cannot match the CustomerId of 1 and so consequently dont result in a query line. With the join types set appropriately, they will return a Null CustomerId, and hense return a row into the query.

(c) With (a) and (b) considered, you should now be able to resort back to your simpler "Count(JobRecords.JobID)" counter to count the jobs per category; ie. you should'nt need all of the @IIF bits.


(d) Assuming no changes to the join types, your corrected query should look something like this:

SELECT ProblemCategory.ProblemCategory,
Count(JobRecords.JobID) AS JobsInCat
FROM CustomerContracts INNER JOIN (CoveredSites INNER JOIN (ProblemCategory right JOIN JobRecords ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID
WHERE (CustomerContracts.CustomerID=1 Or CustomerContracts.CustomerID IS NULL)
GROUP BY ProblemCategory.ProblemCategory


Hope that this finally does the trick,
Cheers,
Steve

 

[jamiec1]

hi steve

that query returns the same results as b4 ..
the cust id is a variable that i have to pass in via an asp page so it will change, and the recordset must return only job records for a specific customer. the problem is that i need to see all the categories in the recordset .. what is happening is that i am only seeing categories where there is a corresponding record in the jobrecords table.. ie not geting zeros in the report..
thanks for your help though.. this problem is causing me no end of head scratching

 

[Steve101]

Well Jamie, I've run out of ideas, though your ASP code should include the double CustomerId constraint.
Hope you sort it out,
Cheers,
Steve