IIf statement with Between

S

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
 
S

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')))
Click to expand...
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 <--
 
S

Steven

Stefan,

I dont understand the equation yet but thank you.

Steven

Stefan Hoffmann said:
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')))
Click to expand...
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 <--
Click to expand...
 
S

Stefan Hoffmann

hi Steven,
I dont understand the equation yet but thank you.
[Forms]![F1]![Qtr] = CLng(CLng([Month]) / 3 + 0.2)
Click to expand...
Click to expand...
Simply enter, e.g.

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

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


mfG
--> stefan <--
 
J

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
 
S

Steven

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

John Spencer said:
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
Click to expand...
Click to expand...
 
Top