Weird error messages all of a sudden

[SusanJane sjl]

I have a user getting weird error messages that didn't used to come up. They
don't come up on my machine. I have Office 2003 and he has Office 2000. My
database is in Access 2000 format. The messages:

1.) The field is too small to accept the amount of data you attempted to add.

2.) The select statement uses a reserved word or an argument name that is
misplaced or missing.

In each case these messages are coming up when the user tries to open a form
that refers to about 100 fields and has selection criteria such as type ="B"
or Name <>"Brown". In one case there are 2 memo fields included.

Could this be a service pack screw-up? I have no idea how to resolve this.

Please help!!

 

[SusanJane sjl]

--
Always grateful for help!!

I don't know about the first error statement. Is that the exact
wording? My guess is it may involve a memo filed, is that right?

The second statement could be the use of a reserved name like " Date"

The thing that did strike me was the part about 100 fields. That
usually means the table design is in error. It is very rare to need more
than say 25 fields if the data is properly normalized. Improperly
normalized data can lead to a number of problems.

What really has me puzzled is why I don't get any error message on my
machine, and why he didn't have these error messages a 2 weeks ago.

The first error message doesn't have memo fields, but the second one does.

Regarding reserved names - are you saying I can't have a field named Date?

I understand about the 100 fields. I HATE this database, but couldn't
figure out any other way to get all this info on one screen, which is what
they wanted.

Thanks so much for your reply.

 

[Allen Browne]

Several things could cause this.

First thing to try would be to compact the database:
Tools | Database Utilities | Compact

If that doesn't work, the user may need to update his JET 4:
http://support.microsoft.com/kb/239114

A field named Name can certainly give this problem. You might be able to
work around it by adding the table name and square brackets, e.g.:
[Table1].[Name] <> "Brown"
Fields have a Type property, so it's just possible that it could give
problems as well. For a list of the names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

If you have criteria on all 100 fields you could be hitting Access limits.
A2003 handles 99 ANDs in the WHERE clause. It was only 50 in some earlier
versions, IIRC.

This one relates to running a query:
http://support.microsoft.com/kb/896950/en-us

There was a kb article which I can't see right now, but it was to do with
too much stuff in table design. You may be able to cut back on some
properties, or some fields. 100 fields suggests the table may not be
correctly normalized. Particularly, if you have repeating fields (e.g.
Item1, Item1Price, Item2, Item2Price, ... or perhaps Jan, Feb, Mar, ..., or
Category1, Category2, ...) these fields need to be broken out into many
records in a related table instead of many fields in the one table.