Custom Filter Form using Combos

[Keith]

A2003, Xp Pro.

I've designed a form which contains 6 of combos. Three on the left are
set up to select fields from a query, three on the right are set up to
select values from the corresponding field selected on the left.

I use these combos to dynamically create a filter string to apply to my
main form. It all works fine as long as I select text fields using
quotation marks. My question is how do I handle different data types
such as numbers and currency?

I've tried using CStr() in a query but I get a 'You cancelled the
previous operation' (?!!) error when the filter is "applied".

Incidentally, I know I'm duplicating the 'filter by form' function but
there are reasons for this.

All advice gratefully received.

Regards,
Keith.

[Wayne Morgan]

Re: Custom Filter Form using Combos

I tried the Eval function, but got an error if the field was a text field.
Strange error, all it said was "Unknown".

Example:
WHERE fldMyField = Eval("23")
or
WHERE fldMyField = Eval("ABC")

The Eval function should return a string or number, as appropriate.

Since that didn't work, you will need to get the Field.Type property for the
field and adjust your syntax to match. Another option may be to not
concatenate the data from the combos on the right into the SQL string.
Instead, pass a reference to those combos and let the query get the values
from the combos.

Example:
WHERE fldMyField = Forms!frmMyForm !cboCombo1

Don't forget, that the Value of the combo box comes from its Bound Column,
which may or may not be the visible column. If it isn't you'll either need
to refer to the Column property or allow for the value that is being passed.

--
Wayne Morgan
MS Access MVP


"Keith" <keith@NOCARPke ithwilby.org.uk > wrote in message
news:cvdbev$2o3 $1@titan.btinte rnet.com...[color=blue]
> A2003, Xp Pro.
>
> I've designed a form which contains 6 of combos. Three on the left are
> set up to select fields from a query, three on the right are set up to
> select values from the corresponding field selected on the left.
>
> I use these combos to dynamically create a filter string to apply to my
> main form. It all works fine as long as I select text fields using
> quotation marks. My question is how do I handle different data types such
> as numbers and currency?
>
> I've tried using CStr() in a query but I get a 'You cancelled the previous
> operation' (?!!) error when the filter is "applied".
>
> Incidentally, I know I'm duplicating the 'filter by form' function but
> there are reasons for this.
>
> All advice gratefully received.
>
> Regards,
> Keith.[/color]