IIf statement with Between

  • Thread starter Thread starter Steven
  • Start date Start date
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
 
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 <--
 
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...
 
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 <--
 
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
 
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...
 
Back
Top