R
Ron
Hi,
I have a simple Access 2007 form that displays a handful of fields from a
single SQL 2005 table.
If I try to use the Filter icons on the toolbar, e.g. "Selection" or
"Advanced/Filter by Form", 95% of the time I receive an error message:
"Cannot apply Filter on one or more fields specified in the Filter Property"
Here are a variety of conditions:
1. I can use the "Filter by Form" and specify a filter on one field, but if
I click "Apply Filter", then go back to "Filter by Form" and add a filter on
another column, I get the error above. However, if I first choose "Clear all
filters" from the Advanced menu, then enter BOTH filter expressions in the
same "visit" to the Filter by Form screen, then the filter works fine.
2. If I use the "selection" button on a field that is not in the primary
key index of the SQL Table, when I look at the filter expression saved, it
puts the other field's filter expression in the primary key's filter
expression. For example, my table has a single-field primary key "Job
Number". But If I specify a filter on "Zip Code = "45803", then I look at
the stored filter expression, it is saved as "Job Number = "45803".
3. If I use the Filter "Funnel" icon, and choose "Text Filters", and choose
any operator (e.g. contains, begins with, greater than, etc.) on any field
that is NOT in the primary key, I receive an error message "Enter a valid
value".
This is a VERY simple form, created with the form wizard. I just pointed to
a table in my connnected SQL database, selected a handful of fields, and was
done.
Some additional info - I don't know if it's relevant or not - The SQL 2005
database has a case-sensitive collating sequence.
Has anyone encountered either of these errors? Doing an internet search on
this error message reports one or two forum messages similar to this one, but
no reference to an explanation of the message itself. The additional help
displayed in Access is:
"This error is usually caused by a mismatch in the data type listed in the
bound column for the row source in the Lookup field. If the ControlSource
property of either the combo box or list box has a different data type than
is listed for the field in the database, this error will appear.
To resolve this error, change the data type of either the control source or
the bound column of the Lookup field to match the correct fields."
But the form was created using the form wizard and all the fields are
VARCHAR anyway.
Any ideas?
Thx in advance,
Ron
I have a simple Access 2007 form that displays a handful of fields from a
single SQL 2005 table.
If I try to use the Filter icons on the toolbar, e.g. "Selection" or
"Advanced/Filter by Form", 95% of the time I receive an error message:
"Cannot apply Filter on one or more fields specified in the Filter Property"
Here are a variety of conditions:
1. I can use the "Filter by Form" and specify a filter on one field, but if
I click "Apply Filter", then go back to "Filter by Form" and add a filter on
another column, I get the error above. However, if I first choose "Clear all
filters" from the Advanced menu, then enter BOTH filter expressions in the
same "visit" to the Filter by Form screen, then the filter works fine.
2. If I use the "selection" button on a field that is not in the primary
key index of the SQL Table, when I look at the filter expression saved, it
puts the other field's filter expression in the primary key's filter
expression. For example, my table has a single-field primary key "Job
Number". But If I specify a filter on "Zip Code = "45803", then I look at
the stored filter expression, it is saved as "Job Number = "45803".
3. If I use the Filter "Funnel" icon, and choose "Text Filters", and choose
any operator (e.g. contains, begins with, greater than, etc.) on any field
that is NOT in the primary key, I receive an error message "Enter a valid
value".
This is a VERY simple form, created with the form wizard. I just pointed to
a table in my connnected SQL database, selected a handful of fields, and was
done.
Some additional info - I don't know if it's relevant or not - The SQL 2005
database has a case-sensitive collating sequence.
Has anyone encountered either of these errors? Doing an internet search on
this error message reports one or two forum messages similar to this one, but
no reference to an explanation of the message itself. The additional help
displayed in Access is:
"This error is usually caused by a mismatch in the data type listed in the
bound column for the row source in the Lookup field. If the ControlSource
property of either the combo box or list box has a different data type than
is listed for the field in the database, this error will appear.
To resolve this error, change the data type of either the control source or
the bound column of the Lookup field to match the correct fields."
But the form was created using the form wizard and all the fields are
VARCHAR anyway.
Any ideas?
Thx in advance,
Ron