if open then ... else ...

[QB]

I am trying to render a query a little more flexible by making it first check
is a given form is open, if so use it's value as a filter else show all the
records, but am having problems. Below is the WHERE clause (which isn't
working)

WHERE
(((Year([qry_rpt_accrual01].[dtStatut]))=IIf(IsOpen("frm_rpt_Accrual")=False,"*",(Year([qry_rpt_accrual01].[dtStatut]))<=[Forms]![frm_rpt_Accrual]![cbo_anneefltr])));

It always prompt for the [Forms]![frm_rpt_Accrual]![cbo_anneefltr] value????
I have validated the IsOpen function does work properly (returning
True/False).

Thank you for the helping hand!

QB

 

[John Spencer]

WHERE
(((Year([qry_rpt_accrual01].[dtStatut]))=
IIf(IsOpen("frm_rpt_Accrual")=False,"*",
(Year([qry_rpt_accrual01].[dtStatut])) <=
[Forms]![frm_rpt_Accrual]![cbo_anneefltr])));

The query is ALWAYS going to insist that frm_rpt_Accrual is open since
it checks for the presence of all fields, tables, and other referred
objects BEFORE it does anything else.

One way to handle your situation is to write a custom function that does
this

Public Function fGet_anneefltr () as String

If IsOpen("frm_rpt_Accrual") Then
fGet_anneefltr = [Forms]![frm_rpt_Accrual]![cbo_anneefltr]
Else
fGet_anneefltr = "*"
End If

End Function

Now your where clause is
Year([qry_rpt_accrual01].[dtStatut]) = fGet_anneefltr()

That might be a problem, since Year(some date) returns a NUMBER and
fGet_anneefltr returns a string. Also ="*" is going to return zero
records. Perhaps you meant to use LIKE "*". That will probably work in
Access since Access will do a conversion of the Number to text.


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