Problem with IIF statement on a Query by Form

[taconi]

Hello,

This is driving me slightly insane, so I would really appreciate any help you could offer.

I am designing a query based on a form and I am trying to get the correct criteria expression to return records between an earliest year and a latest year, including if one or other of them (or both) is left blank.

I have two text boxes on the form (QBF_Form), which are named [WhatEarliestYear] and [WhatLatestYear] and they are set up as Long Integers.

Someone suggested that I enter the [Year] field into two separate columns on my query design view and put the following expressions in:

IIF(IsNull([Forms]![QBF_Form]![WhatLatestYear]), [Year], <=[Forms]![QBF_Form]![WhatLatestYear])

IIF(IsNull([Forms]![QBF_Form]![WhatEarliestYear]), [Year], >=[Forms]![QBF_Form]![WhatEarliestYear])

This makes complete sense to me. However, I cannot get even one of these IIF statements to work. If nothing is entered in either the [WhatEarliestYear] or [WhatLatestYear] boxes on the form, all records are returned, which is exactly what I want. However, if one year is entered in one box and the other left blank, then no records are returned, when what I want is for all records in or after an earliest year entered to come back, or all records in or before a latest year to come back.

Any suggestions?

Thanks in advance.

 

[gemma-the-husky]

IIF(IsNull([Forms]![QBF_Form]![WhatLatestYear]), [Year], <=[Forms]![QBF_Form]![WhatLatestYear])

this won't work. The iif statement is trying to say

iif(condition, then return value a, else return value b), so in those terms all it is doing is

iif(isnulltest, [year], a less than test), which makes no sense at all

so if you have a field called year in your table

in the year column, criteria row put

between [Forms]![QBF_Form]![WhatEarliestYear and [Forms]![QBF_Form]![WhatLatestYear]

try it first with dates eg between 2002 and 2004 to make sure its selecting properly, then replace the dates with the forms lookup.

 

[Brianwarnock]

This may be difficult to explain, but you need to end up with a where statement like below, I'll leave you to replace earliest and latest with forms etc, also less brackets than shown are actually needed.

Brian


WHERE (((Year([yourdatefield])) Between [earliest] And [latest])) OR (((IIf(IsNull([earliest]),Year([yourdatefield])<=[latest]))=True)) OR (((IIf(IsNull([latest]),Year([yourdatefield])>=[earliest]))=True));

 

[EMP]

........ I am trying to get the correct criteria expression to return records between an earliest year and a latest year, including if one or other of them (or both) is left blank.

I assume [Year] is a numeric field.
Try these criteria in two new columns in the query grid in query Design View:

- - - - - - - - - - - - -
Field: [Year]<=[Forms]![QBF_Form]![WhatLatestYear] Or IsNull([Forms]![QBF_Form]![WhatLatestYear])

Show: uncheck

Criteria: True
- - - - - - - - - - - - -

Field: [Year]>=[Forms]![QBF_Form]![WhatEarliestYear] Or IsNull([Forms]![QBF_Form]![WhatEarliestYear])

Show: uncheck

Criteria: True
- - - - - - - - - - - - -

See Jon K's thread for explanations:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[Brianwarnock]

Good catch EMP, makes my approach look cumbersome, and not quite there , if on the right lines.
One final point for taconi , the criteria True , must be on the same ROW as you are ANDING these conditions.

Brian