count on date values using Iif function

svgelacio

Registered User.
Local time
Tomorrow, 05:23
Joined
Apr 6, 2009
Messages
11
I wanted to use IIf function Iif(IsNull([EVF Date]),1,[EVF date]) I also tried using Iif([EVF Date]=" ",1,[EVF Date]) but I get =Error on both expressions when Im running the report. All I want was to put the value 1 when my field is blank (EVF Date is in medium date format) so that i can count the number of blank fields later on which im still figuring out how to if I will place it on the rightmost part of my report(same row). Could anyone please help me with this? I need your prompt reply PLEASE...
 
Add another column to your query and in the field row put something like this count_null:Iif(IsNull([EVF Date],1,0). So that a 1 will indicate a NULL date, then put this field on each row of your report. Use the Sum or Count to give you the number of null fields.
 
thanks, so I really have to do it first in a query, im working on it directly on the report (control source). the error is =IIf(IsNull([ECV Date]),1,[ECV Date]) on this part of the expression and the return value for iif(IsNull([ECV Date]) is 30-Dec-1899, does it mean that its not Null after all? but its totally blank, i mean i didnt put any data on some of my records. Please help me with this.
 
I assume you have a field on your report for the date.

Now in the textbox that you want for the count you put the following in the record source =Iif(IsNull([EVF Date],1,0). The textbox should not be formatted for a date but as a integer etc.
 
okay its working, one more question please. how can i convert now the field so that it will show the actual date field I entered in the form. like (EVF Date) supposedly (medium date) 30-December-2008 but is now showing (39,999).
 
I would leave the date field EVF Date alone, but in the field that you want to count the nulls, presumably the Report footer put =Sum(IIf(Isnull([EVF Date]),1,0)).


BTW blanks in names are a pain leading to syntax errors, ACCESS can do no checking for you and when you use VBA intellisense is useless.

Brian
 

Users who are viewing this thread

Back
Top Bottom