and or statements together

  • Thread starter Thread starter Afia
  • Start date Start date
A

Afia

Hi, I wonder if some one can help me.
The following code works:
Milestone:
IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Completed",IIf(IsNull([Kdcr
Global 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),"Late","On
Target but Milestone > Closure Date")))

But when I modify statement for Late by combining 2 statements (And/Or) for
the result Late, I get error message "The expression you entered has a
function containg the wrong number of arguments.
Appreciate your help in finding out what I'm doing wrong.

Milestone:
IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Completed",IIf(IsNull([Kdcr
Global 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 [Kdcr
Global Roadmap]![Site Status] = "Sites to Rationalise" And
[dbo_Site_ProjectCard_Milestones]![TaskFinish] < Date(),"Late","On Target but
Milestone > Closure Date"))))

thanks
 
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


Afia said:
Hi, I wonder if some one can help me.
The following code works:
Milestone:
IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Complete
d",IIf(IsNull([Kdcr
Global Roadmap]![DcCloseActualDate]) And ([Kdcr Global
Roadmap]![DcCloseTargetDate]>=[dbo_Site_ProjectCard_Milestones]![TaskFinish]
),"On
Target",IIf([Kdcr Global Roadmap]![Site Status]="Sites Closed" And
Click to expand...
( said:
Target but Milestone > Closure Date")))

But when I modify statement for Late by combining 2 statements (And/Or) for
the result Late, I get error message "The expression you entered has a
function containg the wrong number of arguments.
Appreciate your help in finding out what I'm doing wrong.

Milestone:
IIf([dbo_Site_ProjectCard_Milestones]![TaskPercentageComplete]=100,"Complete
d",IIf(IsNull([Kdcr
Global 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 [Kdcr
Global Roadmap]![Site Status] = "Sites to Rationalise" And
[dbo_Site_ProjectCard_Milestones]![TaskFinish] < Date(),"Late","On Target but
Milestone > Closure Date"))))

thanks
Click to expand...
 
Back
Top