Strange IIF statement

[Adrianna]

I keep getting strange errors saying that I've got an extra ")" but I have three of each.....I can't be that tired because only half of the day has past. Here's my statement:

IIF(((IsNull[Approval Date])AND(Not IsNull[Operation Date])),[Operation Date],"")

Basically I have to provide a report by System Type that reports the total number of Operation Dates that have not yet received Approval dates. I just wanted to get this to work before adding the conditions for a valid Operation Date.

So, if there is an easier way to use a WHERE statement in the COUNT function that will allow me to count the dates, where the operation date is valid and the System has not yet been Approved.....I'd love a little insight!

 

[Tim K.]

You need () for the fields.

IIf(((IsNull([Approval Date])) And (Not IsNull([Operation Date]))), [Operation Date], "")


You can use DCount()

Dim I as Integer
I = DCount("YourFieldName","YourTableName","Not IsNull([YourFieldName]) And [Your2ndFieldName] = ...")

 

[Adrianna]

OOPS...I meant to post to REPORTS

Sorry.. I don't think it's going to work right because I have this information in a report and the report provides the data grouped based upon the Type of System.

I'm using an unbound text box in the report to COUNT the total number of Operation_Dates, Where the record WITH an Operation_Date does NOT HAVE an Approval_Date.

SO
If IsNull[Approval Date] AND Not IsNull[Operation Date] Then
Count[Operation Date]
End If

But I'm pretty sure that I'll have to stick with the IIF in the unbound text box.

I just keep getting a "extra )" error when i know that I have 3 open and 3 closed.