Query by Form

[Guest]

I have a form that executes the following
"DoCmd.RunCommand acCmdFilterByForm" and supplies an filterable form.
My "Filter Lookup" property on all controls is set to "Always".

The problem is I would like expressions/calculations from my underlying
query to populate the choices for filtering. I do not want to store these
expressions/calculation in the table because it is so easy for them to become
out of synch with the base data.

Any ideas?

 

[Allen Browne]

If the form is based on a query, and you have the calculated fields in the
query, then you should be able to display those calculated query fields on
the form and use Filter By Form with them.

You won't be able to filter by controls that have an expression in the
ControlSource though.

If you are really stuck, it would be possible to build your own filtering
mechanism, and include the calcuation in the filter string. This kind of
thing:
Dim strWhere As String
strWhere = "([Quantity] * [PriceEach] * (1 + [TaxRate]) >= " &
Me.txtMinAmount
Me.Filter = strWhere
Me.FilterOn = True

More info on building your own filter interface:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

(BTW, don't give up on your choice NOT to store the calculated fields. Spot
on.)

 

[Guest]

You are right in that I can query on those expressions, the problem is when I
want to choose from a list of choices from the underlying query I don't get
all possible expressions, just the table controls.

I have an expression, rtnval:field1*10. The control choices are all possible
field1, not field1*10.

Am I missing something?

If the form is based on a query, and you have the calculated fields in the
query, then you should be able to display those calculated query fields on
the form and use Filter By Form with them.

You won't be able to filter by controls that have an expression in the
ControlSource though.

If you are really stuck, it would be possible to build your own filtering
mechanism, and include the calcuation in the filter string. This kind of
thing:
Dim strWhere As String
strWhere = "([Quantity] * [PriceEach] * (1 + [TaxRate]) >= " &
Me.txtMinAmount
Me.Filter = strWhere
Me.FilterOn = True

More info on building your own filter interface:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

(BTW, don't give up on your choice NOT to store the calculated fields. Spot
on.)

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

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

I have a form that executes the following
"DoCmd.RunCommand acCmdFilterByForm" and supplies an filterable form.
My "Filter Lookup" property on all controls is set to "Always".

The problem is I would like expressions/calculations from my underlying
query to populate the choices for filtering. I do not want to store these
expressions/calculation in the table because it is so easy for them to
become
out of synch with the base data.

Any ideas?

 

[Allen Browne]

Ah, right: Access won't have a list of values to give you for calculated
fields.

I think that's a limit of FBF. I'm not sure if this would work but I suppose
you could try using a combo in the field, and settings its RowSource to
something like this:
SELECT DISTINCT [Field1] * 10 AS RtnVal
FROM Table1;

IME, Filter By Form is of limited use once you want to do serious stuff. It
also doesn't work in MDE/runtime.

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

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

You are right in that I can query on those expressions, the problem is
when I
want to choose from a list of choices from the underlying query I don't
get
all possible expressions, just the table controls.

I have an expression, rtnval:field1*10. The control choices are all
possible
field1, not field1*10.

Am I missing something?

If the form is based on a query, and you have the calculated fields in
the
query, then you should be able to display those calculated query fields
on
the form and use Filter By Form with them.

You won't be able to filter by controls that have an expression in the
ControlSource though.

If you are really stuck, it would be possible to build your own filtering
mechanism, and include the calcuation in the filter string. This kind of
thing:
Dim strWhere As String
strWhere = "([Quantity] * [PriceEach] * (1 + [TaxRate]) >= " &
Me.txtMinAmount
Me.Filter = strWhere
Me.FilterOn = True

More info on building your own filter interface:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

(BTW, don't give up on your choice NOT to store the calculated fields.
Spot
on.)

I have a form that executes the following
"DoCmd.RunCommand acCmdFilterByForm" and supplies an filterable form.
My "Filter Lookup" property on all controls is set to "Always".

The problem is I would like expressions/calculations from my underlying
query to populate the choices for filtering. I do not want to store
these
expressions/calculation in the table because it is so easy for them to
become
out of synch with the base data.