Type mismatch error Access 2000-2002

  • Thread starter Thread starter Lucio Cicolecchia
  • Start date Start date
L

Lucio Cicolecchia

Hi there,

We have been working on an Access 2002 database saving it in Access 2000
format, however on some reports the underling query changes without any
apparent reason generating a Type mismatch error. Any of our attempts to
correct the problematic link in the two tables of the query are not recorded
generating the same error.

Is anybody aware of an existing bug and the way of solving it, or where I
can download the appropriate patch for what seems to be a bug?

Many thanks in advance for any help you can provide.

Lucio

--
______________________________________
Lucio Cicolecchia
25 Marshall Road
Cambridge
CB1 7TY

Tel (Mob): +44-0775 - 361 3766
Tel (Home): +44-01223- 24 24 23
http://www.cambridgeminds.co.uk/people/lucio_cicolecchia.htm
 
Post the SQL and the data types of the fields in question.

In most cases, a table restructure would solve the problem. At worst, you
can force the link between the tables in a where clause using a conversion
function.

i.e.
WHERE cstr(table1.field1) = table2.field1


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Thanks for the reply Steve,

The SQL is:

SELECT Leads.Primary, Leads.[Lead Type], Leads.Line, Lines.[Short definition], Reports.Area, Directors.Surname, Lines.Commissioner, Lines.[Line Status], Lines.[Reporting interval], Lines.[Collection point], Leads.Organisation, Leads.Position
FROM Reports INNER JOIN (Directors INNER JOIN (Lines INNER JOIN Leads ON Lines.Line = Leads.Line) ON (Directors.Directorate = Lines.Directorate) AND (Directors.Organisation = Lines.Organisation)) ON Reports.Area = Lines.Line;

The problem we have is that the SQL is correct (it joins Directors INNER JOIN (Lines INNER JOIN Leads ON Lines.Line = Leads.Line) However, if we check the query builder window we find that the inner join is to a different field (i.e. AREA IN LEADS). If we correct the link into the query builder window (QBW), it appears that the change is recorded, however, as soon as we run the query it gives the Type mismatch error. A check of the QBW shows that the erroneous link has been re-created.

When opening the database in Access 2002 this problem disappears. Any idea? We are going mad!

Lucio
 
Back
Top