[AccessD] Access 97 compact & repair causing report error

You might try upgrading your Jet SP to level 7 or 8
Access 97 used Jet 3.51. It will work with Jet  DAO 3.60 or Jet 4.0
It may not solve your problem
http://support.microsoft.com/kb/303528/

O'Connor, Patricia (OTDA) wrote:

>I tested the database both backend and front end from a coworkers
>computer that has not been upgraded to windows xp.  Problem still
>occurred if compacted backend or created a new backend and copied tables
>to it.
>I then noticed that both our machines only had Access 97 SP1. My older
>machine had SP2.  
>
>So I upgraded my Access 97 to sp2, tried everything again and still get
>error 
>3071 - This expression is typed incorrectly, or it is too complex to be
>evaluated. For example, a numeric expression by assigning parts of the
>expression to variables.
>
>I tried stepping through report to see where the error was occurring but
>it popped up right away. So then I checked the queries used to create
>the report and found that the where statement is what is doing it. If I
>take out one of the or's the query runs. Leave them both in the error
>happens. 
>This system has 25 pages that are generated from 5-6 report templates
>though queries. This is the only query and report that is affected if I
>compact or create a new backend.  
>
>Does anyone know what is causing this error or what I need to do to my
>version of ACCESS 97 so that is doesn't do this.  I will be upgrading
>this entire system to Access 2000 then 2003 but I need to have it stable
>in 97 for a bit longer.  I am just really getting back to full time work
>after bad accident a year ago. So I would greatly appreciate any and all
>help. 
>
> Below is the query that is affected. I am working on this to see if I
>can get around problem.
>
>SELECT tblBicsRpt.RptDt,
> tblBicsRpt.DistrictNo, 
>IIf([TANF],1,IIf([SN],2,"C")) AS Source, 
>Sum(tblBicsRpt.Cases) AS qCases, 
>Sum(tblBicsRpt.Recipients) AS qRecipients, 
>Sum(tblBicsRpt.Children) AS qChildren, 
>Sum(tblBicsRpt.Adults) AS qAdults, 
>Sum(Int([Expenditures]*10^0+0.5)/10^0) AS qExpenditures
>FROM tlkpXrefDescID INNER JOIN tblBicsRpt ON (tlkpXrefDescID.ItemNo =
>tblBicsRpt.ItemNo) AND (tlkpXrefDescID.Schedule = tblBicsRpt.ScheduleNo)
>WHERE (
>((tblBicsRpt.DistrictNo <>66) and
>(IIf(InStr([Forms]![FrmBicMoSel]![txtSelCriteria],[tlkpXrefDescID]![Case
>Type])>0,True,False)=True)) 
>or 
>((tblBicsRpt.DistrictNo = 66)  and (tlkpXrefDescID.LineId In
>(16,70,137))) 
>)
>GROUP BY tblBicsRpt.RptDt, tblBicsRpt.DistrictNo,
>IIf([TANF],1,IIf([SN],2,"C"))
>HAVING (tblBicsRpt.RptDt Between [Forms]![FrmBicMoSel]![dtBegin] 
>And [Forms]![FrmBicMoSel]![dtEnd]) 
>AND ((IIf([TANF],1,IIf([SN],2,"C"))) In (1,2));
>
>Thanks everyone
>******************************************************************
>*Patricia O'Connor
>*Associate Computer Programmer Analyst
>*OTDA - BDMA
>*(W) mailto:Patricia.O'Connor at otda.state.ny.us
>*(w) mailto:aa1160 at otda.state.ny.us
>******************************************************************
> 
>
>  
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada