I went through your last iif statement and tried to break it down to
the appropriate AND and ORs.

Your problem is the position of the right parens  )))
You do not have enough of them to the LEFT of the last set of = and
not = values.

Here is how it seems to break down and I hope the word wrap doesn;t
make it too unreadable.

It is confusing to me since I don't know your real intent, but re-
examine your ands and ors

====================
IIf([dbo_Site_ProjectCard_Milestones]!
[TaskPercentageComplete]=100,"Complet­ed",
    IIf(IsNull([KdcrGlobal Roadmap]![DcCloseActualDate]) And ([Kdcr
Global
Roadmap]![DcCloseTargetDate]>=[dbo_Site_ProjectCard_Milestones]!
[TaskFinish­]),"On
Target",
    IIf(
[Kdcr Global Roadmap]![Site Status]="Sites Closed"
    AND
    ([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]<>100)
        OR
              ([dbo_Site_ProjectCard_Milestones]!
[TaskPercentageComplete] <> 100
         And [KdcrGlobal Roadmap]![Site Status] = "Sites to
Rationalise"
         And [dbo_Site_ProjectCard_Milestones]![TaskFinish] < Date(),

        "Late","On Target but Milestone > Closure Date"))))

Some of those last set of )) need to be to the left of "Late" (after
the <Date())
==================

Ron

Afia,

When I have a complicated IIF clause like this, I will generally create a
function that returns the values.  This accomplishes two things:

1.  It allows me to put some error checking into the equation (for example
to test for NULL values).  I don't have any in this function, but you might
consider it.

2.  It also runs quicker.  Immediate If statements evaluate all of the
options before coming up with a solution, so your computed value will
evaluate all of the possible elements of the IIF( ) before returning a value,
on the other hand, you could structure your function so that the most likely
occurance is tested first, then the next most likely, etc.

Public Function fnMilestone(PctComplete as long, _
                                       ActualCloseDate as Date, _
                                       TargetCloseDate as Date, _
                                       TaskFinishDate as Date, _
                                       SiteStatus as string) as String

   IF PctComplete = 100 Then
       fnMilestone = "Completed"
   elseif  ISNULL(ActualCloseDate) AND (TargetCloseDate <= TaskFinishDate)
THEN
       fnMiilestone = "On Target"
   elseif SiteStatus = "Sites Closed" AND PctComplete <> 100 THEN
       fnMilestone = "late"
   else
       fnMilestone = "On Target but milestone > closure date"
   endif

End Function

HTH
Dale

Thats not a function, its an essay! :) I find it helpful to write a function
in a module when it gets to this level.

I'm not certain that it says what you intend it to.
For instance, you start off by saying
IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Complete
d",
That's it, end of story.
Yet further in the function, you add the condition that
[TaskPercentageComplete] <>100. This is unnessary because we know that for
it to have got past the first hurdle, it won't = 100.

It will also help if you have a SiteStatus Table which links to this current
table so that SiteStatus will become an ID number rather than a text field
and a combo box will be used to pick the status. As it is, if someone
mistyped "Sites Closed" as "Site Closed", the function will fail.

I haven't tested this out fully but you seem to have 5 variables

This is what your function SEEMS to say - perhaps it will be clearer when
seen like this
Paste the following into a new module

'just a reminder
'var1 = [TaskPercentageComplete]
'var2 = [DcCloseActualDate]
'var3 =[DcCloseTargetDate]
'var4 =[TaskFinish]
'var5 = [Site Status]

Public Function Milestone(ByVal var1, var2, var3, var4, var5) As String
'use the function with
'MyMileStone:
Milestone([TaskPercentageComplete,[DCCloseActualDate],[DCCloseTargetDate],[T
askFinish],[SiteStatus])
Select Case var1

Case 100
Milestone = "Completed"

Case Is <> 100
'all of the following only apply if TaskPercentageCompleted is not 100
   If IsNull(var2) And var3 >= var4 Then
   'you have no entry in DcCloseActualDate and
[DcCloseTargetDate]>=[TaskFinish]
       Milestone = "On Target"
   End If

   If (var5 = "Sites Closed" Or var5 = "Sites to Rationalise") And var4 <
Date Then
   'not sure if var5 and var 4 need to be bracket together or if the 2 var
5s do. or none do
           Milestone = "Late"
   Else

  Milestone = "On Target but Milestone > Closure Date"
  End If
End Select
End Function

Sub Test()
'test the function with different combinations
Dim My1 As Integer
Dim My2 As Variant
Dim My3 As Date
Dim My4 As Date
Dim My5 As String

My1 = 80
'My2 = #4/27/2008#
My2 = Null
My3 = #4/27/2008#
My4 = #4/23/2008#
My5 = "Sites Closed"
MsgBox Milestone(My1, My2, My3, My4, My5)

End Sub

IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Complete
d",IIf(IsNull([Kdcr

Roadmap]![DcCloseTargetDate]>=[dbo_Site_ProjectCard_Milestones]![TaskFinish]
),"On

([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]<>100),"Late","On

IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Complete
d",IIf(IsNull([Kdcr

Roadmap]![DcCloseTargetDate]>=[dbo_Site_ProjectCard_Milestones]![TaskFinish]
),"On