filter by calculated control

J

Juan

Hi everyone,

I have a form in which I want to filter by two combo boxes. one is working
fine, but the other one is not.
"Hrs Status" is a calculated control. Here is the control source =IIf([Total
Hrs]>=40,IIf([Estimated Hrs]>0,"Done/Unapproved","Done"),"Missing")

Here is the filter function:

Private Function BuildFilter() As Variant
BuildFilter = (" And [Project Administrator] = """ + Me.cboFilterPA + """")
& (" And [Hrs Status] = """ + Me.cboFilterHW + """") & ""
BuildFilter = Mid(BuildFilter, 6)

End Function

Here, "cboFilterHW" has a list of three values:
"Done";"Done/Unapproved";"Missing", which are the three options for "Hrs
Status". When I try to filter by this combo box, i get an input box, as if I
had misspelled the control name (which is not the case).

Does somebody have an idea of what the problem might be?
Thanks a lot!
 
A

Allen Browne

Juan, the simplest way to let Access filter by a calculated field is to make
it part of the form's RecordSource.

You could create a query, and type the expression into the Field row:
Hrs_Status: IIf([Total Hrs]>=40,IIf([Estimated
Hrs]>0,"Done/Unapproved","Done"),"Missing")
Use the query as the source for the form, and you will be able to filter on
the field.

The alternative would be to repeat the entire expression in the Filter
string, instead of referring to Hrs_Status.
 
J

Juan

Thanks a lot Allen!
I did that and it works as expected.


Allen Browne said:
Juan, the simplest way to let Access filter by a calculated field is to make
it part of the form's RecordSource.

You could create a query, and type the expression into the Field row:
Hrs_Status: IIf([Total Hrs]>=40,IIf([Estimated
Hrs]>0,"Done/Unapproved","Done"),"Missing")
Use the query as the source for the form, and you will be able to filter on
the field.

The alternative would be to repeat the entire expression in the Filter
string, instead of referring to Hrs_Status.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Juan said:
I have a form in which I want to filter by two combo boxes. one is working
fine, but the other one is not.
"Hrs Status" is a calculated control. Here is the control source
=IIf([Total
Hrs]>=40,IIf([Estimated Hrs]>0,"Done/Unapproved","Done"),"Missing")

Here is the filter function:

Private Function BuildFilter() As Variant
BuildFilter = (" And [Project Administrator] = """ + Me.cboFilterPA +
"""")
& (" And [Hrs Status] = """ + Me.cboFilterHW + """") & ""
BuildFilter = Mid(BuildFilter, 6)

End Function

Here, "cboFilterHW" has a list of three values:
"Done";"Done/Unapproved";"Missing", which are the three options for "Hrs
Status". When I try to filter by this combo box, i get an input box, as if
I
had misspelled the control name (which is not the case).

Does somebody have an idea of what the problem might be?
Thanks a lot!
Click to expand...
Click to expand...
 
Top