IIf in Queries

  • Thread starter Thread starter Andrew.j.wilkinson
  • Start date Start date
A

Andrew.j.wilkinson

Hi all,

I would like to know how I can us code to replace IIF
statments I use via the "field" section of the QBE window
of some of my queries. I use the IIF to force ouputs
when a particular criteria is true/false.
Heres an example

Start SLA Status: IIf([Customer Commitment Date] Is Not
Null,"SLA Achieved",IIf([StartDateTime] Is Null And
[ApprovalDatetime] Is Null,"No Times Recorded",IIf
([StartDateTime] Is Null,"Start Not Recorded",IIf
([ApprovalDateTime] Is Null,"Approval Not Recorded",IIf
([ApprovalDateTime]>=[StartDateTime],"SLA Achieved",IIf
(DateDiff("n",[ApprovalDateTime],[StartDateTime])<=
[stdStartTime],"SLA Achieved","SLA Missed"))))))


This is obviously very cumbersome and confusing. I feel
a case statement might be a better option but don't know
how I can do this through a query. The result of this
query is exported to an XLS spreadsheet and used later
for analysis.

Hope this is clear enough. Look forward to your reply
Thanks

Andy
 
Can't use Case options in a query. IIf is a normal way of doing this; or, if
you're using numeric values, you can use the Choose function; or, if your
query values allow it, the Switch function can be used for some purposes.

Otherwise, you'd need to write a VBA function that receives the value of
Customer Commitment Date and returns the desired text string for the query
to display; then you could use a Case statement in that function.
 
Sounds like a plan Ken.

However, if I create a VBA function where/how do I call
it an how do I pass the parameters.
-----Original Message-----
Can't use Case options in a query. IIf is a normal way of doing this; or, if
you're using numeric values, you can use the Choose function; or, if your
query values allow it, the Switch function can be used for some purposes.

Otherwise, you'd need to write a VBA function that receives the value of
Customer Commitment Date and returns the desired text string for the query
to display; then you could use a Case statement in that function.

--
Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)"
Click to expand...
wrote in message news:082401c3bece$ee199420 [email protected]...
Hi all,

I would like to know how I can us code to replace IIF
statments I use via the "field" section of the QBE window
of some of my queries. I use the IIF to force ouputs
when a particular criteria is true/false.
Heres an example

Start SLA Status: IIf([Customer Commitment Date] Is Not
Null,"SLA Achieved",IIf([StartDateTime] Is Null And
[ApprovalDatetime] Is Null,"No Times Recorded",IIf
([StartDateTime] Is Null,"Start Not Recorded",IIf
([ApprovalDateTime] Is Null,"Approval Not Recorded",IIf
([ApprovalDateTime]>=[StartDateTime],"SLA Achieved",IIf
(DateDiff("n",[ApprovalDateTime],[StartDateTime])<=
[stdStartTime],"SLA Achieved","SLA Missed"))))))


This is obviously very cumbersome and confusing. I feel
a case statement might be a better option but don't know
how I can do this through a query. The result of this
query is exported to an XLS spreadsheet and used later
for analysis.

Hope this is clear enough. Look forward to your reply
Thanks

Andy
Click to expand...


.
Click to expand...
 
Open a Module from database window. Put the function in that module --
something like this:

Public Function CallMeWhenYouNeedMe(Parameter1 As String, Parameter2 As
String, Parameter3 As String, SelectValue As Integer) As String
Select Case SelectValue
Case 1
CallMeWhenYouNeedMe = Parameter1 & Parameter2 & Parameter3
Case 2
CallMeWhenYouNeedMe = Parameter3 & Parameter1
Case 3
CallMeWhenYouNeedMe = Parameter1 & Parameter2
Case 4
CallMeWhenYouNeedMe = Parameter1
End Select
End Function


Then, in your query, just use the function as a wrapper around your field --
something like this:

CalculatedField: CallMeWhenYouNeedMe([FieldName1], "ThisIsAString",
[FieldName2], 3)


--
Ken Snell
<MS ACCESS MVP>


Sounds like a plan Ken.

However, if I create a VBA function where/how do I call
it an how do I pass the parameters.
-----Original Message-----
Can't use Case options in a query. IIf is a normal way of doing this; or, if
you're using numeric values, you can use the Choose function; or, if your
query values allow it, the Switch function can be used for some purposes.

Otherwise, you'd need to write a VBA function that receives the value of
Customer Commitment Date and returns the desired text string for the query
to display; then you could use a Case statement in that function.

--
Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)"
Click to expand...
wrote in message news:082401c3bece$ee199420 [email protected]...
Hi all,

I would like to know how I can us code to replace IIF
statments I use via the "field" section of the QBE window
of some of my queries. I use the IIF to force ouputs
when a particular criteria is true/false.
Heres an example

Start SLA Status: IIf([Customer Commitment Date] Is Not
Null,"SLA Achieved",IIf([StartDateTime] Is Null And
[ApprovalDatetime] Is Null,"No Times Recorded",IIf
([StartDateTime] Is Null,"Start Not Recorded",IIf
([ApprovalDateTime] Is Null,"Approval Not Recorded",IIf
([ApprovalDateTime]>=[StartDateTime],"SLA Achieved",IIf
(DateDiff("n",[ApprovalDateTime],[StartDateTime])<=
[stdStartTime],"SLA Achieved","SLA Missed"))))))


This is obviously very cumbersome and confusing. I feel
a case statement might be a better option but don't know
how I can do this through a query. The result of this
query is exported to an XLS spreadsheet and used later
for analysis.

Hope this is clear enough. Look forward to your reply
Thanks

Andy
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Ken,

Thanks for you help.
However I couldn't use a Case Statement as I didn't have
a unique test I could use.

Instead I've used If, elseif, Else as indicated below
however the I get an error when I run the query.

Here is the module.

Public Function StartSLAStatus(CCD As Date, StartDateTime
As Date, _
ApprovalDateTime As Date,
StdStartTime As Integer) As String

If CCD Is Not Null Then
SLAStatus = "SLA Achieved"
ElseIf StartDateTime Is Null And ApprovalDateTime Is Null
Then
SLAStatus = "SLA Achieved"
ElseIf StartDateTime Is Null Then
SLAStatus = "Start Not Recorded"
ElseIf ApprovalDateTime Is Null Then
SLAStatus = "Approval Not Recorded"
ElseIf ApprovalDateTime >= StartDateTime Then
SLAStatus = "SLA Achieved"
ElseIf DateDiff("n", ApprovalDateTime, StartDateTime) <=
[StdStartTime] Then
SLAStatus = "SLA Achieved"
Else
SLAStatus = "SLA Missed"
End If

End Function


Her is the string that is calling the function in the
query.

SLAModuleStatus: StartSLAStatus([CustomerCommitmentDate],
[StartDateTime],[ApprovalDateTime],[StdStartTime])

And here is the error.

Undefined function 'StartSLAStatus' in expression.

Any ideas???

Andy



-----Original Message-----
Open a Module from database window. Put the function in that module --
something like this:

Public Function CallMeWhenYouNeedMe(Parameter1 As String, Parameter2 As
String, Parameter3 As String, SelectValue As Integer) As String
Select Case SelectValue
Case 1
CallMeWhenYouNeedMe = Parameter1 & Parameter2 & Parameter3
Case 2
CallMeWhenYouNeedMe = Parameter3 & Parameter1
Case 3
CallMeWhenYouNeedMe = Parameter1 & Parameter2
Case 4
CallMeWhenYouNeedMe = Parameter1
End Select
End Function


Then, in your query, just use the function as a wrapper around your field --
something like this:

CalculatedField: CallMeWhenYouNeedMe
Click to expand...
([FieldName1], "ThisIsAString",
[FieldName2], 3)


--
Ken Snell
<MS ACCESS MVP>


Sounds like a plan Ken.

However, if I create a VBA function where/how do I call
it an how do I pass the parameters.
-----Original Message-----
Can't use Case options in a query. IIf is a normal way of doing this; or, if
you're using numeric values, you can use the Choose function; or, if your
query values allow it, the Switch function can be used for some purposes.

Otherwise, you'd need to write a VBA function that receives the value of
Customer Commitment Date and returns the desired text string for the query
to display; then you could use a Case statement in
Click to expand...
that
function.
--
Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)"
Click to expand...
wrote in message [email protected]...
Hi all,

I would like to know how I can us code to replace IIF
statments I use via the "field" section of the QBE window
of some of my queries. I use the IIF to force ouputs
when a particular criteria is true/false.
Heres an example

Start SLA Status: IIf([Customer Commitment Date] Is Not
Null,"SLA Achieved",IIf([StartDateTime] Is Null And
[ApprovalDatetime] Is Null,"No Times Recorded",IIf
([StartDateTime] Is Null,"Start Not Recorded",IIf
([ApprovalDateTime] Is Null,"Approval Not Recorded",IIf
([ApprovalDateTime]>=[StartDateTime],"SLA Achieved",IIf
(DateDiff("n",[ApprovalDateTime],[StartDateTime])<=
[stdStartTime],"SLA Achieved","SLA Missed"))))))


This is obviously very cumbersome and confusing. I feel
a case statement might be a better option but don't know
how I can do this through a query. The result of this
query is exported to an XLS spreadsheet and used later
for analysis.

Hope this is clear enough. Look forward to your reply
Thanks

Andy


.
Click to expand...
Click to expand...


.
Click to expand...
 
In VBA code, you use the IsNull function to test for Null:

Public Function StartSLAStatus(CCD As Date, StartDateTime As Date, _
ApprovalDateTime As Date,
StdStartTime As Integer) As String

If IsNull(CCD) = False Then
SLAStatus = "SLA Achieved"
ElseIf IsNull(StartDateTime) = True And IsNull(ApprovalDateTime) = True Then
SLAStatus = "SLA Achieved"
ElseIf IsNull(StartDateTime) = True Then
SLAStatus = "Start Not Recorded"
ElseIf IsNull(ApprovalDateTime) = True Then
SLAStatus = "Approval Not Recorded"
ElseIf ApprovalDateTime >= StartDateTime Then
SLAStatus = "SLA Achieved"
ElseIf DateDiff("n", ApprovalDateTime, StartDateTime) <= [StdStartTime] Then
SLAStatus = "SLA Achieved"
Else
SLAStatus = "SLA Missed"
End If

End Function


Be sure that you put this function in a regular module (Modules in the
database window).

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for you help.
However I couldn't use a Case Statement as I didn't have
a unique test I could use.

Instead I've used If, elseif, Else as indicated below
however the I get an error when I run the query.

Here is the module.

Public Function StartSLAStatus(CCD As Date, StartDateTime
As Date, _
ApprovalDateTime As Date,
StdStartTime As Integer) As String

If CCD Is Not Null Then
SLAStatus = "SLA Achieved"
ElseIf StartDateTime Is Null And ApprovalDateTime Is Null
Then
SLAStatus = "SLA Achieved"
ElseIf StartDateTime Is Null Then
SLAStatus = "Start Not Recorded"
ElseIf ApprovalDateTime Is Null Then
SLAStatus = "Approval Not Recorded"
ElseIf ApprovalDateTime >= StartDateTime Then
SLAStatus = "SLA Achieved"
ElseIf DateDiff("n", ApprovalDateTime, StartDateTime) <=
[StdStartTime] Then
SLAStatus = "SLA Achieved"
Else
SLAStatus = "SLA Missed"
End If

End Function


Her is the string that is calling the function in the
query.

SLAModuleStatus: StartSLAStatus([CustomerCommitmentDate],
[StartDateTime],[ApprovalDateTime],[StdStartTime])

And here is the error.

Undefined function 'StartSLAStatus' in expression.

Any ideas???

Andy



-----Original Message-----
Open a Module from database window. Put the function in that module --
something like this:

Public Function CallMeWhenYouNeedMe(Parameter1 As String, Parameter2 As
String, Parameter3 As String, SelectValue As Integer) As String
Select Case SelectValue
Case 1
CallMeWhenYouNeedMe = Parameter1 & Parameter2 & Parameter3
Case 2
CallMeWhenYouNeedMe = Parameter3 & Parameter1
Case 3
CallMeWhenYouNeedMe = Parameter1 & Parameter2
Case 4
CallMeWhenYouNeedMe = Parameter1
End Select
End Function


Then, in your query, just use the function as a wrapper around your field --
something like this:

CalculatedField: CallMeWhenYouNeedMe
Click to expand...
([FieldName1], "ThisIsAString",
[FieldName2], 3)


--
Ken Snell
<MS ACCESS MVP>


Sounds like a plan Ken.

However, if I create a VBA function where/how do I call
it an how do I pass the parameters.
-----Original Message-----
Can't use Case options in a query. IIf is a normal way
of doing this; or, if
you're using numeric values, you can use the Choose
function; or, if your
query values allow it, the Switch function can be used
for some purposes.

Otherwise, you'd need to write a VBA function that
receives the value of
Customer Commitment Date and returns the desired text
string for the query
to display; then you could use a Case statement in that
function.

--
Ken Snell
<MS ACCESS MVP>

"(e-mail address removed)"
<[email protected]>
wrote in message [email protected]...
Hi all,

I would like to know how I can us code to replace IIF
statments I use via the "field" section of the QBE
window
of some of my queries. I use the IIF to force ouputs
when a particular criteria is true/false.
Heres an example

Start SLA Status: IIf([Customer Commitment Date] Is Not
Null,"SLA Achieved",IIf([StartDateTime] Is Null And
[ApprovalDatetime] Is Null,"No Times Recorded",IIf
([StartDateTime] Is Null,"Start Not Recorded",IIf
([ApprovalDateTime] Is Null,"Approval Not Recorded",IIf
([ApprovalDateTime]>=[StartDateTime],"SLA Achieved",IIf
(DateDiff("n",[ApprovalDateTime],[StartDateTime])<=
[stdStartTime],"SLA Achieved","SLA Missed"))))))


This is obviously very cumbersome and confusing. I
feel
a case statement might be a better option but don't
know
how I can do this through a query. The result of this
query is exported to an XLS spreadsheet and used later
for analysis.

Hope this is clear enough. Look forward to your reply
Thanks

Andy


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top