IIF Statement

[Prufrock]

I have a preview form that summarises a range of data about a specific client when you put in their unique ID. This all runs on a query. I have a field in that query which is the date they complete a course. I have created a parameter query which takes that date and runs an iif statement that if that date is less than 6 weeks ( 42 days) from today then YES should appear in that parameter query field.

Currently I have the following:
WithinReviewPeriod:IIf([CompletionDate]<=Date()-42,"YES"," ")
I do not get it stopping YES after the 42 days pass. This displays it always


I know I am close can someone assist me with the correct syntax please?

 

[JerryKlmns]

Your syntax seems Ok. The following should have the same result

WithinReviewPeriod: IIF(DateDiff('ww', [CompletionDate], Date())<5, 'YES', ' ')

 

[jontout]

I have a similar issue with IIF that I'd appreciate a pointer as to why this isn't counting, please.

SELECT tblClaims.RecDate, Count(*) AS Received,
Sum(IIf(ProcessDays Between 1 And 3,1,0)) AS [SLA MET],
Sum(IIf(ProcessDays >3,1,0)) AS [SLA NOT MET],
Sum(IIf(ProcessDays <1,1,0)) AS [INCOMPLETE]
FROM tblClaims WHERE (((tblClaims.RecDate)= #9/26/2007#)

For this particular data, I should have the following figures, but the Incomplete doesn't calculate.

12 Received
5 SLA MET
7 SLA NOT MET
4 INCOMPLETE

Anyone?

Cheers,

Jon

 

[HarleyQuinn]

I'm not understanding how you can receive 12, but then have it break down into 16?

Cheers

HarleyQuinn
---------------------------------
If you never say your name out loud to anyone they can never ever call you by it - Regina Spektor

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

It would probably also be a good idea to start a new thread for your question.

Cheers

HarleyQuinn
---------------------------------
If you never say your name out loud to anyone they can never ever call you by it - Regina Spektor

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Prufrock]

Thanks Jerry

That worked fine.