Count IIf Function in Report

[JithuAccess]

Hi,

In my Report I want to find the Total Number of the Files not returned. I have added the following code in the Coontrol source property of a Text Box

=Count(IIf([File_Return_Date]=IsNull())

and I am getting the Following Error:

Could you please let me know the correct code?

Thanks

 

[Minty]

You are thinking in Excel by the looks of it.
Use a query

SELECT Count(1) as NoOfRecs
FROM YourTable
WHERE [File_Return_Date] Is Null

 

[JithuAccess]

You are thinking in Excel by the looks of it.
Use a query

SELECT Count(1) as NoOfRecs
FROM YourTable
WHERE [File_Return_Date] Is Null

You are thinking in Excel by the looks of it.
Use a query

SELECT Count(1) as NoOfRecs
FROM YourTable
WHERE [File_Return_Date] Is Null

You are thinking in Excel by the looks of it.
Use a query

SELECT Count(1) as NoOfRecs
FROM YourTable
WHERE [File_Return_Date] Is Null

Please note that I don't want to use N Number of queries as we can do it simply by using a IIF Function in the control Source of a Text Box. I am sure there is an Error in my coding and it would be highly appreciate if you could let me know what's the correct Syntax of Count IIF Function.

In my Table there are 25 records. Out of these 18 records there is a value for the field File_Return_Date and 7 records File_Return_Date is Null. So I want to display in the Summary of a report like Total Records: 25 File Returned: 18 File Not Returned: 7.

Thanks

 

[cheekybuddha]

=Count(IIf([File_Return_Date]=IsNull())

At a guess, you probably want:

=Sum(IIf([File_Return_Date] IS NULL, 1, 0))

Or

=Sum(IIf(IsNull([File_Return_Date]), 1, 0))

Or

=-Sum(IsNull([File_Return_Date]))

 

[Minty]

Or if it's small number of records use a DCount() as the control source of the control where you want the total.

= DCount("*","YourTable","[File_Return_Date] IS NULL")

 

[JithuAccess]

At a guess, you probably want:

=Sum(IIf([File_Return_Date] IS NULL, 1, 0))

Or

=Sum(IIf(IsNull([File_Return_Date]), 1, 0))

Or

=-Sum(IsNull([File_Return_Date]))

Thank you so much. It works perfect.

Sorry for bothering you again. I want to add one more criteria. I want to find the total count if the File_Return_Date comes between a certain date range and this is my Code:

=Sum(IIf([File_Return_Date] Is Not Null,1,0)) And [File_Return_Date] Between [Forms]![frmFileStatusInput]![txtBeginDate] And [Forms]![frmFileStatusInput]![txtEndDate]

Unfortunately it's returning Nothing.

Thanks

 

[Pat Hartman]

You have combined two expressions incorrectly.

Sum() works on the bound recordSource
The Where criteria is used when you are looking at a different set of data but that requires you to use the dSum() functions. The function names that start with "d" are domain functions and reference a table or query to obtain their data.

If you need the where clause, use the sample posted by @Minty If you want to "count" using the bound recordSource, use the sample posted by @Uncle Gizmo .

Count() also works on the current recordSource but since it is NOT a domain function, you are ALWAYS looking at the entire RecordSource so if it is 25 records, the answer with Count() will ALWAYS be 25. So, we use sum() to convert presence/absence to 0 or 1 and therefore "count" the records we want.

 

[cheekybuddha]

=Sum(IIf([File_Return_Date] Is Not Null,1,0)) And [File_Return_Date] Between [Forms]![frmFileStatusInput]![txtBeginDate] And [Forms]![frmFileStatusInput]![txtEndDate]

Unfortunately it's returning Nothing.

You don't need both parts. If [File_Return_Date] is between the two dates then it can't be Null.

So, you can also try:

=Sum(IIf([File_Return_Date] Between [Forms]![frmFileStatusInput]![txtBeginDate] And [Forms]![frmFileStatusInput]![txtEndDate],1,0))

(I haven't tested Pat's assertion that this won't work)

Or did you mean that you want a count of records where [File_Return_Date] is either Null or between the two dates?

Count() also works on the current recordSource but since it is NOT a domain function, you are ALWAYS looking at the entire RecordSource so if it is 25 records, the answer with Count() will ALWAYS be 25. So, we use sum() to convert presence/absence to 0 or 1 and therefore "count" the records we want.

You can do the same with DCount(), no?

 

[ebs17]

So I want to display in the Summary of a report like Total Records: 25 File Returned: 18 File Not Returned: 7.

3 results in one calculation, could be inserted via subreport.

SELECT
   COUNT(*) AS TotalRecords,
   SUM(File_Return_Date > 0) * -1 AS FilesReturned,
   SUM(File_Return_Date Is NULL) * -1 AS FilesNotReturned
FROM
   YourTable
WHERE
   File_Return_Date BETWEEN Forms.frmFileStatusInput.txtBeginDate
      AND
   Forms.frmFileStatusInput.txtEndDate

You can see that you could add any other filter criteria (you would have to be able to handle parameter passing). It should be ensured that the main report and subreport access the same data source with the same filtering.

 

[Pat Hartman]

@ebs17 That solution is great but it cannot be used directly in the ControlSource. The OP could create a querydef and then in the controlSource use a dLookup() to return one of the three values as the single result he wants.

 

[Pat Hartman]

I haven't tested Pat's assertion that this won't work

I didn't say that your rendition of the expression wouldn't work. I said the one the OP used would not work. Your solution would probably work but you'd have to check it. As long as the code is running in the referenced form, I would NOT use the long name reference, I would use

=Sum(IIf([File_Return_Date] >= [txtBeginDate] And [File_Return_Date] <= [txtEndDate],1,0))

Also, Between is an SQL expression. It may work in this expression but I can't say for sure.

 

[ebs17]

it cannot be used directly in the ControlSource

Correct. But my suggestion was also to use this query as a record source for a subreport. For the report with exactly one record, you can place the assigned controls anywhere you want and garnish them with some text.
You actually love bound forms and reports, and I don't like constant DLookup fire.

 

[cheekybuddha]

As long as the code is running in the referenced form, I would NOT use the long name reference

Initially I thought the same, but then I realised that the control is on a report (if the forum selected is accurate) and the referenced controls are on a form (so my guess, a different form)

 

[Pat Hartman]

OK, but I also don't know if Between will work.

 

[Pat Hartman]

You actually love bound forms and reports, and I don't like constant DLookup fire.

I don't like constant DLookup() fire either. HOWEVER - this function returns ONE value based on dates that don't change so it would be placed in the header of the form/report and run only once. If the op wants all three values then embedding a form/report in the page header would be a good solution that requires no code.

 

[JithuAccess]

Hello,

Thank you so much for all your replies.

Is there any method to find the Total Number of Records in the Table in a Report?

The Record Source of my Report is a Query and I want to find the Total Number of Records in a Table Because Query is based on certain criteria and I want to find the total number of the records in the Table. It's working in the Query builder but when I copy the same code and paste it in the Control Source of my Text Box (I have placed that Text Box in the Report Header of my Report) I am getting Error. This is my Code:

 =SELECT Count([tblFileRequestData.File_Number]) AS CountOfFile_Number FROM [tblFileRequestData];

I am getting the following Error Message.

Thanks

 

[cheekybuddha]

OK, but I also don't know if Between will work.

Nor do I; I never use it due to issues with unseen time portions.

Your expression would be better with < Int([txtEndDate] + 1)

 

[ebs17]

A query returns a recordset (an object with rows and columns, even with one row and one column this is something other than a single value), but a control only accepts a single value. The simplest solution here would be to use a domain aggregate function, where the query is packed into a function and returns a single value.

= DCount("*", "tblFileRequestData")

If you want to follow the subreport idea above, this query can also be integrated into the query shown.

SELECT
   (
      SELECT
         COUNT(*)
      FROM
         tblFileRequestData
   ) AS TotalRecordsTable,
   COUNT(*) AS TotalRecordsQuery,
   SUM(File_Return_Date > 0) * - 1 AS FilesReturned,
   SUM(File_Return_Date Is NULL) * - 1 AS FilesNotReturned
FROM
   AnyQuery
WHERE
   File_Return_Date BETWEEN Forms.frmFileStatusInput.txtBeginDate
      AND
   Forms.frmFileStatusInput.txtEndDate

 

[Pat Hartman]

You don't need to reference the recordsource or run a domain function. You just use the built in features.

Add a control in the Report's Footer section. Make the ControlSource: =Count(*)

If you have breaks in the report, you can put the same expression in the footer of each section and the control will show the count for the specific section.

 

[JithuAccess]

You don't need to reference the recordsource or run a domain function. You just use the built in features.

Add a control in the Report's Footer section. Make the ControlSource: =Count(*)

If you have breaks in the report, you can put the same expression in the footer of each section and the control will show the count for the specific section.

Thank you so much. It works.

Could you please let me know the correct syntax to put a range of dates?

This is my code in the Report Activate Event

Private Sub Report_Activate()

Dim BDate As Date
Dim EDate As Date

BDate = [Forms]![frmReturnedFileStatusInput]![txtBeginDateFIlesReturned]
EDtae = [Forms]![frmReturnedFileStatusInput]![txtEndDateFIlesReturned]

End Sub

And this is what I put in the control Source of my Text Box

=DCount("*","tblFIleRequestData","FIle_Requested_Date between #Bdate# and #Edate#")

Thanks