Hi everyone, I would like to have some advice on my FTSearch query. I try to get all document between to dates or where the date is empty and when my FTSearch line is executed, I have the following error:
4005 - Relational operators are not supported in text fields
and my query is :
FIELD FormName = “Training” & (((FIELD TrainEvalDate >= [2005-13-04]) & (FIELD TrainEvalDate <= [2005-15-04])) | TrainEvalDate == “”))
Thanks in advance
Sim Bou
Subject: RE: FTSearch date problem
This message is returned by the full-text search engine (FTSearch method, or a manual search from a view) when you use the < or > operator on a field whose datatype is text.
If you are getting this error when calling FTSearch method, try the same query in the search bar from a view. You should get the same error. If your query is complex, try the different parts of it to discover which clause is causing the problem.
The full-text engine does not support searching for a string which is alphabetically “before” or “after” some other string. Date/time and number fields can be searched in this way, but if you’re getting this error searching for a date or number value, it means that the field is really not a date/time or number field, at least in the opinion of the full-text engine. For instance, if your search is
DateDue > 1/4/2009
then the problem is that the full-text search considers DateDue to be a text field, not a date field.
The full-text index records a datatype for each field used in your application, based on the data stored in the documents. Once it has decided on the datatype for a particular name, it never changes its mind. You would have to delete and re-create the full-text index to get it to notice that a field’s datatype has changed.
This decision is not made based on the datatype you have selected for the field when designing a form. The full-text indexer ignores forms, and only looks at your documents. If some documents contain a text value for the field, and some contain date/time values, the indexer will make the decision based on the first document it sees where the field is not blank. If you’re uncertain what values are being stored in your documents, use the document properties dialog from a view – not from a document window – and use the second tab to see the field names and values. The datatype and value of the selected field are shown on the right side of the dialog.
Editing a form and changing the datatype of a field there, will not change existing documents that have the wrong datatype. You would also have to write an agent to update your documents and convert the field to the right datatype. Then, once there are no documents containing text in that field, delete and re-create the full-text index.