IIf Statement in Subform Query

[tonyrusin]

Hi,

I have a subform based on a query the uses criteria from the parent
form. So, depending on what information is filled in the text boxes on
the main form, the subform will filter based on that. I have all of it
working but one part I'm having trouble with is a number range using
an IIf statement.

Here is the syntax I'm having issues with (built in the query builder
and converted to SQL):

SELECT tblTaskBoat.*
FROM tblTaskBoat
WHERE (((tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoStart]),(tblTaskBoat.WeekNo)>=[Forms]![frmSchedule]![txtWeekNoStart])
And (tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoEnd]),(tblTaskBoat.WeekNo)<=[Forms]![frmSchedule]![txtWeekNoEnd])));

The Criteria field in the query builder has this:

IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoStart]),>=[Forms]![frmSchedule]![txtWeekNoStart])
And IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoEnd]),>=[Forms]![frmSchedule]![txtWeekNoEnd])

I started simple just using a half of the query (not attempting a date
range) but I can't even get that to work:

SELECT tblTaskBoat.*
FROM tblTaskBoat
WHERE (((tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoStart]),(tblTaskBoat.WeekNo)>=[Forms]![frmSchedule]![txtWeekNoStart])));

Criteria:

IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoStart]),>=[Forms]![frmSchedule]![txtWeekNoStart])

What I'm after is if the field on the parent form is null then I
don't want the query to filter based on it, if it's not null then
show greater than or equal to that number or vise-versa for the other
field.

Any help is greatly appreciated. Thanks!

- Tony

 

[Guest]

Hi Tony,

I have a subform based on a query the uses criteria from the parent
form. So, depending on what information is filled in the text boxes on
the main form, the subform will filter based on that. I have all of it
working but one part I'm having trouble with is a number range using
an IIf statement.

Here is the syntax I'm having issues with (built in the query builder
and converted to SQL):

SELECT tblTaskBoat.*
FROM tblTaskBoat
WHERE (((tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoStart]),(tblTaskBoat.WeekNo)>=[Forms]![frmSchedule]![txtWeekNoStart])
And (tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoEnd]),(tblTaskBoat.WeekNo)<=[Forms]![frmSchedule]![txtWeekNoEnd])));

The IIF() syntax is wrong. It should be

IIF(<condition>, <result if TRUE> , <result if FALSE>)

You are missing the FALSE part.

More importantly, the WHERE clause is wrong. It looks like you want to limit
the records returned using the field "WeekNo", which (looks like it) is an
integer. So th e IIF() should reutrn an integer.

The Criteria field in the query builder has this:

IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoStart]),>=[Forms]![frmSchedule]![txtWeekNoStart])
And IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoEnd]),>=[Forms]![frmSchedule]![txtWeekNoEnd])

I don't understand this.

I started simple just using a half of the query (not attempting a date
range) but I can't even get that to work:

SELECT tblTaskBoat.*
FROM tblTaskBoat
WHERE (((tblTaskBoat.WeekNo)=IIf(Not
(tblTaskBoat.WeekNo)=IsNull([Forms]![frmSchedule]![txtWeekNoStart]),(tblTaskBoat.WeekNo)>=[Forms]![frmSchedule]![txtWeekNoStart])));

Same thing as above

Criteria:

IIf(Not
IsNull([Forms]![frmSchedule]![txtWeekNoStart]),>=[Forms]![frmSchedule]![txtWeekNoStart])

Same as above.

What I'm after is if the field on the parent form is null then I
don't want the query to filter based on it, if it's not null then
show greater than or equal to that number or vise-versa for the other
field.

Based on the above paragraph, I came up with the following SQL.
I am assuming that you will never have a [weekno] that is less than -1000
and the [weekno] will never be greater than 1000.


SELECT tblTaskBoat.*
FROM tblTaskBoat
WHERE
(((tblTaskBoat.weekno)>=IIf(IsNull([Forms]![frmSchedule]![txtWeekNoStart]),-1000,[Forms]![frmSchedule]![txtWeekNoStart])
And
(tblTaskBoat.weekno)<=IIf(IsNull([Forms]![frmSchedule]![txtWeekNoEnd]),1000,[Forms]![frmSchedule]![txtWeekNoEnd])))
ORDER BY tblTaskBoat.weekno;


HTH