Multiple parameters to nested iif statement.

Question

Post reply

Multiple parameters to nested iif statement.

gallan-229957

SSCommitted

Points: 1520
More actions
June 28, 2005 at 11:10 am

#113943

Hi there,
I've got a query with 3 parameters.
The first is for location that can be left blank of the user fills in a textbox.
Parameters!Location
The second is a date range in days (120, 90, 60).
Parameters!DateRange
The third is a textbox that they can use to enter a date (dd/mm/yyyy/).
Parameters!ExplicitDate
Either the second or the third parameters are needed, in other words they are an OR.
My question is can I nest an iif statement within another or should I go about this a different way.
Ex. iif(Parameters!Location.Value <> "", iif(Parameters!DateRange.Value <> "", "select * from myTable where myLocation = '" & Parameters!Location.Value & '" and (DATEDIFF(day, myTable.myDate, getDate()) <= '" & Parameters!DateRange.Value & "')", "select * from myTable where myLocation = '" & Parameters!Location.Value & "' and myTable.myDate = '" & Parameters!ExplicitDate & "'",iif(Parameters!DateRange.Value <> "", "select * from myTable where (DATEDIFF(day, myTable.myDate, getDate()) <= '" & Parameters!DateRange.Value & "')", "select * from myTable where and myTable.myDate = '" & Parameters!ExplicitDate & "'" Ugly and convoluted not to mention it doesn't work...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

finding it difficult to follow your logic, but you could do something like this:

If(Parameters!Location.Value > "") then

if(Parameters!DateRange.Value > ""

select * from myTable

where myLocation = '" & Parameters!Location.Value & '" and

(DATEDIFF(day, myTable.myDate, getDate()) <= '" & Parameters!DateRange.Value &

else

select * from myTable

where myLocation = '" & Parameters!Location.Value & "' and

myTable.myDate = '" & Parameters!ExplicitDate & "'"

end if

elseif (Parameters!Location.Value = "")....etc...

Hope this is what you were looking for....

**ASCII stupid question, get a stupid ANSI !!!**

gallan-229957 SSCommitted Points: 1520 More actions · Answer 2

The If statements you wrote are the correct logic, the problem is that I'm writing this in the Data pane of Visual Studio and the syntax is iif, not the vb syntax. Unless I can use VB there...can I?

Martin N Ten Centuries Points: 1216 More actions · Answer 3

Couldn't you just do this in the SQL? e.g.

SELECT * FROM mytable

WHERE (@Location = Location)

AND (

(@Date = Date)

OR (@DateRange >= DATEDIFF(dd, Date, GETDATE()))

)

Or am i missing something?

Kindest Regards,

Martin

gallan-229957 SSCommitted Points: 1520 More actions · Answer 4

But if @Date or @DateRange is empty wouldn't that crap out? Also, this looks like something from a stored procedure or query analyzer, I don't think this syntax would work in a report. I'll have to test it.

Martin N Ten Centuries Points: 1216 More actions · Answer 5

i was thinking that your code was being used to select records into your report ie in the data pane, if so then you can use SQL or a stored proc... the code wouldn't crap out if the paramter had a NULL value but the user would not get any records returned. You could handle for this using ISNULL or a default value maybe...

Kindest Regards,

Martin

gallan-229957 SSCommitted Points: 1520 More actions · Answer 6

Yes I'm using the data pane but I need to check if the @Location parameter has been entered yet. If it is use it in the WHERE clause, otherwise don't.

This is why I'm using an expression:

="select * from mytable"

instead of:

select * from mytable

because I'm incorporating the iif syntax.