IIf command in query statement

J

Joel

Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between [Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate field
for my Start Date and End Date user entry. This works well, but I want to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in my
query?

Thank you for your help.
-Joel
 
F

Frederick Wilson

Joel said:
Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between [Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate field
for my Start Date and End Date user entry. This works well, but I want to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in my
query?

Thank you for your help.
-Joel
Click to expand...
This might not be the best way but when things get complex I make my own
formula in a module.

So

THIS IS AIR CODE FOR STIMULATING IDEAS ONLY. NOT GURENTEED TO WORK

Function FindWhatDate(strStage as string, InitiateDate as date) as date

select case strstage
case "NEW"
FindWhatDate=InitiateDate

case "Complete"
more code
End select

End function

Then you use this function in the critera of the query.

Hope this gets you started.
Fred
 
S

Sylvain Lafontaine

Use it in the false part (or the third parameter) of the IIF statement:

IIf([DrawingStage]="New",[JobInitiatedDate], [DrawingCompleteDate])

or:
IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

In your case, using conventional logical statement instead of an IIF
statement is a simpler solution:

Having ([DrawingStage]="New" and ([JobInitiatedDate] Between [Start Date]
And [End Date]))
OR
([DrawingStage]="Complete" and ([DrawingCompleteDate] Between [Start
Date] And [End Date]))

S. L.
 
J

Joel Allen

Thank you Fred.

Frederick Wilson said:
Joel said:
Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between [Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate
field
for my Start Date and End Date user entry. This works well, but I want
to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in my
query?

Thank you for your help.
-Joel
Click to expand...
This might not be the best way but when things get complex I make my own
formula in a module.

So

THIS IS AIR CODE FOR STIMULATING IDEAS ONLY. NOT GURENTEED TO WORK

Function FindWhatDate(strStage as string, InitiateDate as date) as date

select case strstage
case "NEW"
FindWhatDate=InitiateDate

case "Complete"
more code
End select

End function

Then you use this function in the critera of the query.

Hope this gets you started.
Fred
Click to expand...
 
J

Joel Allen

Thank you Sylvain, this worked perfectly since I have 4 IIF statements!

IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

Thank you!


Sylvain Lafontaine said:
Use it in the false part (or the third parameter) of the IIF statement:

IIf([DrawingStage]="New",[JobInitiatedDate], [DrawingCompleteDate])

or:
IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

In your case, using conventional logical statement instead of an IIF
statement is a simpler solution:

Having ([DrawingStage]="New" and ([JobInitiatedDate] Between [Start Date]
And [End Date]))
OR
([DrawingStage]="Complete" and ([DrawingCompleteDate] Between [Start
Date] And [End Date]))

S. L.

Joel said:
Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between [Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate
field
for my Start Date and End Date user entry. This works well, but I want
to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in my
query?

Thank you for your help.
-Joel
Click to expand...
Click to expand...
 
J

Joel Allen

Shoot, it didn't work like I thought it did!

This works but I need to use the IIF for 4 possible answers, not just 2.

Date:IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Released For Fab",[CompleteReleasedForFabricationDa]))

I tried this and it doesn't work:

Date: IIf([DrawingStage]="New",[JobInitiatedDate]) or
IIf([DrawingStage]="Shipped",[ShippedDate])
or IIf([DrawingStage]="Cancelled",[CancelledDate]) or
IIf([DrawingStage]="Released",[ReleasedDate])

I think I just have a syntax leaning to do. Can you please help again?

Thanks - Joel


Joel Allen said:
Thank you Sylvain, this worked perfectly since I have 4 IIF statements!

IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

Thank you!


Sylvain Lafontaine said:
Use it in the false part (or the third parameter) of the IIF statement:

IIf([DrawingStage]="New",[JobInitiatedDate], [DrawingCompleteDate])

or:
IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

In your case, using conventional logical statement instead of an IIF
statement is a simpler solution:

Having ([DrawingStage]="New" and ([JobInitiatedDate] Between [Start Date]
And [End Date]))
OR
([DrawingStage]="Complete" and ([DrawingCompleteDate] Between [Start
Date] And [End Date]))

S. L.

Joel said:
Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between [Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate
field
for my Start Date and End Date user entry. This works well, but I want
to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in my
query?

Thank you for your help.
-Joel
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

IIf([DrawingStage]="New",[JobInitiatedDate],
IIF ([DrawingStage]="Shipped",[ShippedDate],
IIF ([DrawingStage]="Cancelled",[CancelledDate],
IIF ([DrawingStage]="Released",[ReleasedDate],
))))

With more than 2 IIF, you are probably better with the other method; which
is more easier to read/write.

S. L.

Joel Allen said:
Shoot, it didn't work like I thought it did!

This works but I need to use the IIF for 4 possible answers, not just 2.

Date:IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Released For Fab",[CompleteReleasedForFabricationDa]))

I tried this and it doesn't work:

Date: IIf([DrawingStage]="New",[JobInitiatedDate]) or
IIf([DrawingStage]="Shipped",[ShippedDate])
or IIf([DrawingStage]="Cancelled",[CancelledDate]) or
IIf([DrawingStage]="Released",[ReleasedDate])

I think I just have a syntax leaning to do. Can you please help again?

Thanks - Joel


Joel Allen said:
Thank you Sylvain, this worked perfectly since I have 4 IIF statements!

IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

Thank you!


Sylvain Lafontaine said:
Use it in the false part (or the third parameter) of the IIF statement:

IIf([DrawingStage]="New",[JobInitiatedDate], [DrawingCompleteDate])

or:
IIf([DrawingStage]="New",[JobInitiatedDate], IIF
([DrawingStage]="Complete",[DrawingCompleteDate]))

In your case, using conventional logical statement instead of an IIF
statement is a simpler solution:

Having ([DrawingStage]="New" and ([JobInitiatedDate] Between [Start
Date] And [End Date]))
OR
([DrawingStage]="Complete" and ([DrawingCompleteDate] Between [Start
Date] And [End Date]))

S. L.

Hello,

I'm trying to use this in a query. This is what it looks like in SQL:

HAVING ((((IIf([DrawingStage]="New",[JobInitiatedDate]))) Between
[Start
Date] And [End Date]));

It says that if the DrawingStage is New, then use the JobInitiatedDate
field
for my Start Date and End Date user entry. This works well, but I want
to
also say that if the DrawingStage is Complete, then use the
DrawingCompleteDate field.

In other words, how do I use two IIF statements in a single column in
my
query?

Thank you for your help.
-Joel
Click to expand...
Click to expand...
Click to expand...
 
Top