Filter data types

[hughess7]

Hi all

I have a search facility on a form in my database. When the form is opened
it displays all the fields used from a particular table ([DealerClaimCheck])
with all records displayed (read-only). They will only search one field at a
time, no requirement for multi-criteria as yet at least. I have coded it so
that if a user double-clicks in any of the fields, that fields value will be
stored as the search criteria in an unbound text box ([txtSearch]) and the
control name is stored in an unbound combo box ([]). They can also select the
field to search manually from the combo box and enter the criteria in the
text box. I now need to create the filter code and I have a question ... how
can I use the one text box to be able to accept entries of any data type? ie
I know if they enter a date it will need formatting for jet to understand it
and text requires quotes etc etc.

I did find an excellent search utility in these forums which does what i
want to achieve, but unfortunately it isn't ideal for an end user to use and
it is too complicated for me to understand exactly how it works! But it did
seem to interpret the data type correctly from an unbound text box.

I know I can have seperate text boxes for each data type but is it possible
to perform any search from just the one? I don't want to limit which fields
can be filtered on either.

Thanks in advance for any help.

 

[hughess7]

Sorry submitted too early, missed off the name of the unbound combo box which
has a rowsource Field Type based on a query, it is called [FieldSearch] ...

 

[John Spencer]

An unbound text box will accept any kind of data.

You can test the type of data in the text box using
IsDate and IsNumeric and IsNull and then convert it as needed using the
conversion functions.

How are you populating the FieldSearch combobox? You could add the field type
as a hidden column in the combobox and then use that to decide what you were
going to use.

You might be able to use the BuildCriteria function to build your criteria.
BuildCriteria attempts to build the filter string the same way as when you
enter criteria in the Query design view (the query grid).

Check out BuildCriteria in the VBA help.
Check out "Type Property DAO" in the Access help.

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

 

[hughess7]

Thanks John, I am populating the combo by setting its Row Source Type to
Field List
and using a query as the Row Source to give meaningful field names to the
user using aliases in the query, and to set the field sequence and sort order.

I'll have a look at BuildCriteria in the help thx.

Sue

An unbound text box will accept any kind of data.

You can test the type of data in the text box using
IsDate and IsNumeric and IsNull and then convert it as needed using the
conversion functions.

How are you populating the FieldSearch combobox? You could add the field type
as a hidden column in the combobox and then use that to decide what you were
going to use.

You might be able to use the BuildCriteria function to build your criteria.
BuildCriteria attempts to build the filter string the same way as when you
enter criteria in the Query design view (the query grid).

Check out BuildCriteria in the VBA help.
Check out "Type Property DAO" in the Access help.

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

Hi all

I have a search facility on a form in my database. When the form is opened
it displays all the fields used from a particular table ([DealerClaimCheck])
with all records displayed (read-only). They will only search one field at a
time, no requirement for multi-criteria as yet at least. I have coded it so
that if a user double-clicks in any of the fields, that fields value will be
stored as the search criteria in an unbound text box ([txtSearch]) and the
control name is stored in an unbound combo box ([]). They can also select the
field to search manually from the combo box and enter the criteria in the
text box. I now need to create the filter code and I have a question ... how
can I use the one text box to be able to accept entries of any data type? ie
I know if they enter a date it will need formatting for jet to understand it
and text requires quotes etc etc.

I did find an excellent search utility in these forums which does what i
want to achieve, but unfortunately it isn't ideal for an end user to use and
it is too complicated for me to understand exactly how it works! But it did
seem to interpret the data type correctly from an unbound text box.

I know I can have seperate text boxes for each data type but is it possible
to perform any search from just the one? I don't want to limit which fields
can be filtered on either.

Thanks in advance for any help.