IIf statement with Between

[Steven]

I am trying to create a Select query that will return the records for a
particular quarter depending on the value of a textbox [Qtr] in form "F1".

Is something like this possible in the criteria of a query, I cannot make it
work:

Field: Month ..... it is a text field in the table

Criteria: IIF([Forms]![F1]![Qtr]=1, Between '01' and
'03',IIF([Forms]![F1]![Qtr]=2,Between '04' AND '06',IIF([Forms]![F1]![Qtr]=3,
Between '07' AND '09',Between '10' AND '12')))

Thank you for your help,

Steven

 

[Stefan Hoffmann]

hi Steven,

Field: Month ..... it is a text field in the table

Criteria: IIF([Forms]![F1]![Qtr]=1, Between '01' and
'03',IIF([Forms]![F1]![Qtr]=2,Between '04' AND '06',IIF([Forms]![F1]![Qtr]=3,
Between '07' AND '09',Between '10' AND '12')))

Storing the month as text is not a good idea. But this criteria should
still work:

[Forms]![F1]![Qtr] = CLng(CLng([Month]) / 3 + 0.2)


mfG
--> stefan <--

 

[Steven]

Stefan,

I dont understand the equation yet but thank you.

Steven

hi Steven,

Field: Month ..... it is a text field in the table

Criteria: IIF([Forms]![F1]![Qtr]=1, Between '01' and
'03',IIF([Forms]![F1]![Qtr]=2,Between '04' AND '06',IIF([Forms]![F1]![Qtr]=3,
Between '07' AND '09',Between '10' AND '12')))

Storing the month as text is not a good idea. But this criteria should
still work:

[Forms]![F1]![Qtr] = CLng(CLng([Month]) / 3 + 0.2)


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Steven,

I dont understand the equation yet but thank you.

[Forms]![F1]![Qtr] = CLng(CLng([Month]) / 3 + 0.2)

Simply enter, e.g.

? CLng(CLng("11") / 3 + 0.2)

In the immediate window in the VBA IDE and press enter...


mfG
--> stefan <--

 

[John Spencer]

Month is a reserved word and is not a good name for a field. It can lead to
problems. You can construct your criteria using something like the following.
Although Stefan Hoffman's solution is creative and should work for this
specific case.

Field: [Month]
Criteria: Between IIF([Forms]![F1]![Qtr]=1, '01'
,IIF([Forms]![F1]![Qtr]=2,'04'
,IIF([Forms]![F1]![Qtr]=3, '07','10')))
AND
IIF([Forms]![F1]![Qtr]=1, '03'
,IIF([Forms]![F1]![Qtr]=2,'06'
,IIF([Forms]![F1]![Qtr]=3, '09','12')))

You can't set the comparison operators in an expression, you can set the
values. Note that the comparison operators are outside the IIF clauses in the
above.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Steven]

Yes, Nice, thank you. That helps me on something else also.

Month is a reserved word and is not a good name for a field. It can lead to
problems. You can construct your criteria using something like the following.
Although Stefan Hoffman's solution is creative and should work for this
specific case.

Field: [Month]
Criteria: Between IIF([Forms]![F1]![Qtr]=1, '01'
,IIF([Forms]![F1]![Qtr]=2,'04'
,IIF([Forms]![F1]![Qtr]=3, '07','10')))
AND
IIF([Forms]![F1]![Qtr]=1, '03'
,IIF([Forms]![F1]![Qtr]=2,'06'
,IIF([Forms]![F1]![Qtr]=3, '09','12')))

You can't set the comparison operators in an expression, you can set the
values. Note that the comparison operators are outside the IIF clauses in the
above.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to create a Select query that will return the records for a
particular quarter depending on the value of a textbox [Qtr] in form "F1".

Is something like this possible in the criteria of a query, I cannot make it
work:

Field: Month ..... it is a text field in the table

Criteria: IIF([Forms]![F1]![Qtr]=1, Between '01' and
'03',IIF([Forms]![F1]![Qtr]=2,Between '04' AND '06',IIF([Forms]![F1]![Qtr]=3,
Between '07' AND '09',Between '10' AND '12')))

Thank you for your help,

Steven