IIf command in query statement

  • Thread starter Thread starter Joel
  • Start date Start date
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
 
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
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top