Access 2003 Table sort type mismatch error

[Guest]

I'm attempting to sort a table in Access on a field which uses a dropdown
list linked to another table. Access refuses to sort the table, instead
giving me a Type Mismatch error and destroying the file! Once closed, the
table will no longer open in datasheet view and the attached forms will not
open in form view. I have to restore from a previous copy, losing all work
in the process. Why can't I perform a simple sort? Thanks.

 

[tina]

couple recommendations:
one, don't use Lookup fields in tables, period. (combo boxes in *forms* are
fine.) see http://www.mvps.org/access/lookupfields.htm for reasons why.

two, don't manipulate data directly in tables. that's what queries and forms
are for. see the 10 commandments of Access at
http://www.mvps.org/access/tencommandments.htm , #2.
if you want to sort your data for display or editing purposes, then sort it
in a query and bind the query to a form or report - or just sort the data
directly from the form or report.

hth

 

[Guest]

Thanks for the recommendations, Tina, but they really don't answer my
question, especially since I have over 10,000 records in this database. At
issue is the fact that I am not a professional database designer and yet
Access is directly marketed to us end users as a 'simple' desktop application
bundled with the rest of MS Office.

You know, this litany of 'I know Access lets you do X, but you should NEVER
do X, but do Y instead' is getting tiresome - no offense to you, personally,
Tina. The fact is that the end user does not have time to spend all day with
nose in book or eyes glued to the Internet researching all the caveats of
using an expenseive, licensed product. A few 'bugs' here and there I can
understand, but what you're saying is that Access is deliberately designed to
fail when certain features of the application - marketed as a part of the
standard functionality of the product - are utilized.

If sorting tables is not the 'proper' thing to do, then why does Access
provide the user tools with which to do it? If Lookup Tables are 'evil,'
then why does Access put the user through the wringer when attempting to
establish joins that would result in successful queries, and yet provide a
Wizard to create those Lookups?

This all started when I was attempting to do the 'right' thing (or so I
thought) by creating a query then using the Wizard to create a subform. The
response I got from another Access expert when this failed? Don't use
subforms! LOL

Sorry, Tina...to the end user, the application's UI is the place we start,
and what we rely on to give us accurate information not only about the
functions of the product, but of the feasibility of utilizing those
functions. When the phrase 'type mismatch' cannot even be found anywhere in
the Help file, then I know that Microsoft is still not taking their users
seriously.

Thanks.

 

[tina]

no problem, Greg, i'm not a Microsoft employee or in any way associated with
Microsoft or with the development of the Access product, so i'm not offended
at all.

the fact is that Access is a very complex product that allows a user, who
has no prior training or skills as a computer programmer, to build complex
relational databases to support complex business processes. (i'm one of
those many users who has no other programming experience or skills, outside
of Access.)

but Access is *not* simple to learn to use, in the way that Word or even
Excel are simple; it has a steep learning curve, and to "unlock" and
leverage its' enormous power to manipulate and analyze data requires a
considerable investment of time, reading/study, and practice. and it
requires learning not only how to use the software itself, but also learning
to apply the rules of relational data modeling, and (eventually) learning to
work with VBA programming language, which is common to the other products in
Office Suite as well. (though you can do quite a bit in Access *without* the
use of VBA, by using macros - which are completely different from "macros"
in Word, etc, and very powerful by comparison.)

as for the specific issue of the Lookup fields in tables, i agree with you
that they shouldn't be a part of the product, because of all the problems
they cause. but you and i don't make the product, so...

btw, if someone told you to solve a subform problem by not using a subform
*in that particular situation*, that may well have been very good advice.
but if you were advised to not use subforms AT ALL, then i'd say that person
is not an expert in Access. <g>