Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access97ErrorMsg (SP1 NOW, SP2 BEFORE.)

    Hello, Up until today my queries were running fine and now I keep getting an error message from everyone that uses a specific query for a field. The error message is: Compile Error in Query Expression. I have 50+ queries using the same expression and they all bomb out now. I reloaded a 2-week old DB and it worked fine, then when I copied the individual queries that seemed to be corrupt, boom! Again, not working. I even went as far as reloading Office and no help......H E L P!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access97ErrorMsg (SP1 NOW, SP2 BEFORE.)

    Have you tried to compact and repair your database - it sounds suspiciously like a corruption problem. Make a copy just in case the repair decides to throw something away. And I would go back to SP2 - there are some useful fixes in it.
    Wendell

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97ErrorMsg (SP1 NOW, SP2 BEFORE.)

    Yes. Compact and Repair did nothing. I did re-evaluate the function and re-wrote them to work, however in one instance I received a circular reference. The function not working is: Mileage: IIf(IsNull([DealerDefined1]),[Vehicle]![Mileage],[DealerDefined1]). When I take out the ISNull then circular reference rears its head, so I am left to do this: Miles: IIf([Mileage]=0,[DealerDefined1],[Mileage]).

    This seems simple enough, but even at SP2 I get notta. I have noticed that 97 will let you do some things wrong then all of a sudden it comes to life like this and not let you past go..... And, I am using a 2-week uncorrupted database plus the one that went belly up. With the two acting the same, I'll have to concede that perhaps the functions where not right to begin with??

    What do you think??

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access97ErrorMsg (SP1 NOW, SP2 BEFORE.)

    Your analysis seems correct. The function doesn't appear to have a circular reference, although I assume that there are a number of other calculated fields in your query. Are these stored queries, or are they SQL strings in code or as datasources in forms. If your query string gets too long, Access will choke, and it doesn't always return a meaningful error message. It's not documented in the Help file, but I believe the maximum length of a SQL String used as a data source for a report or form is 2048 characters. Another possibility is that the query is too complex for the query grid to evaluate. In that case, you typically want to break it up into two or more queries and do a query on query(ies). If none of this leads anywhere, try simplifying it to the point where you can post it, and I'll take a look.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •