Why do I receive an error when filtering on a field?

[jdeeken]

I have a field TxComments which is set as a long text and the field size is long integer. This field contains various text. I have been able to filter in the past, but all of a sudden I receive the error "enter a valid value". My search filter is- contains: *rza*. Is there a limit on records that an Access Database can have; is this maybe limiting me to filter? My current database has approx. 15,756 records. I have filtered this down to 24 records and still the TxComments field will not filter and gives me the above error. Any suggestions/thoughts.

[CJ_London]

I have been able to filter in the past, but all of a sudden I receive the error "enter a valid value".

something will have changed - don't understand this

have a field TxComments which is set as a long text and the field size is long integer

have you changed something about the field?

Is there a limit on records that an Access Database can have; is this maybe limiting me to filter?

No - subject to the overall 2gb limit for the database size. 15k records is not very many at all.

Without knowing any more difficult to suggest a solution - you could compact and repair. Otherwise provide more detail - is db split? does each user have their own front end? provide some example data, explain how you are filtering - using vba?, filtering in a query or table?

[ssanfu]

I have a field TxComments which is set as a long text and the field size is long integer.

This doesn't make sense to me.
Beginning in Access 2013, Memo fields are now called Long Text fields. A Long text type field can hold 65,535 characters when entering data through the user interface and 1 gigabyte of character storage when entering data programmatically.
A Long Text type field does not have a field size of Long Integer.....????? What am I missing?

[jdeeken]

I don't think I've changed anything with this field database view or design view. I looked in the property sheet to see if I could see anything and nothing looks odd. This is not a split database. I have given access to several other admin in the group, but I have locked fields so they cannot change, so filtering shouldn't be affected. When I'm filtering I right click on the field, choose text filters, choose contains and enter what I'm trying to locate (i.e. *rza*). In some of my files I have notes that indicates "ID RZA 3/28/18". I am using some characters that are not searchable such as "!" so not sure if that would be causing the problem, but I'm not searching for those anyway. I've been manually removing some of my old notes in the TxComments field to see if that will help, so far it has not. Thanks for any other thoughts or suggestions.

[June7]

If you select 'contains' there is no need for the * wildcards, although should not hurt.

And yes, makes no sense to say that a Long Text field has Long Integer size.

If you want to provide db for analysis, follow instructions at bottom of my post.

[CJ_London]

This is not a split database. I have given access to several other admin in the group,

the consequence of this is that you may have caused the db to become corrupted and could be the reason your search is no longer working. You should always split the database and give each user their own copy of the front end

[jdeeken]

Thx Ajax -- is there a way that I can repair the db? I will look into splitting the database. Thanks for your help.

[CJ_London]

compact and repair, then split the database.

If compact and repair does not work, then create a new blank db and import the tables. This would be your backend. create another new db and link to the tables in your new backend, then import the forms, reports, queries and modules. This will be your new front end - send a copy to each user to install on their desktop/documents. There are many ways to update the front end when you make changes in the future - you'll find them all on this and other forums. Any issues with importing implies the corruption is not repairable, in which case you will need to recreate as necessary. For tables it may be a case of identifying the corrupt record or records, importing the rest, then manually recreating the missing records.

Think of the front end as being an application (which it is) - users don't share Excel, Word etc - they each have their own copy, same applies to Access. The backend happens to be access, actually ACE or JET for 2003 and earlier, and is designed to allow multi user access, same as any other rdbms, the front end is not. Unfortunately MS tend not to emphasis this unless you look into their documentation.