Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query error (A2002)

    I've got a query with several parameters. The 2 date parameters are not a problem, the "enter program" parameter is. When I type in 2 of the 6 possible programs the query works fine. When I type in any of the other 4 I get this error: "This expression is typed incorrectly, or is too complex to be evaluated."

    Here's the SQL:
    SELECT tblProgObj.ProgObjID, tlkpObj.ObjName, Sum(Abs([Approved])) AS NumApproved, Sum(Abs([Achieved])) AS NumAchieved, [NumAchieved]/[NumApproved] AS PercAchieved, Avg(tblConsProgObj.Amount) AS AvgOfAmount, tlkpUnits.Unit, qStandard.Standard, tlkpProg.ProgramName
    FROM tlkpUnits INNER JOIN ((tblConsumers INNER JOIN (tlkpProg INNER JOIN tblConsProg ON tlkpProg.ProgramID = tblConsProg.ProgramID) ON tblConsumers.ConsumerID = tblConsProg.ConsumerID) INNER JOIN (tlkpObj INNER JOIN (qsubObjSumDates INNER JOIN ((qStandard INNER JOIN tblProgObj ON qStandard.ProgObjID = tblProgObj.ProgObjID) INNER JOIN tblConsProgObj ON (tblProgObj.ProgObjID = tblConsProgObj.ProgObjID) AND (tblProgObj.ProgObjID = tblConsProgObj.ProgObjID)) ON qsubObjSumDates.ConsProgID = tblConsProgObj.ConsProgID) ON (tlkpObj.ObjID = tblProgObj.ObjID) AND (tlkpObj.ObjID = tblProgObj.ObjID)) ON tblConsProg.ConsProgID = tblConsProgObj.ConsProgID) ON tlkpUnits.UnitID = tblProgObj.UnitID
    GROUP BY tblProgObj.ProgObjID, tlkpObj.ObjName, tlkpUnits.Unit, qStandard.Standard, tlkpProg.ProgramName
    HAVING (((tlkpProg.ProgramName)=[enter program]))
    ORDER BY tblProgObj.ProgObjID;

    I can't figure out why it will take 2 of the 6 programs and not the others. Any ideas?

    esw

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: query error (A2002)

    I'd suggest you look at the program names that are causing problems and tell us what is in them. If you have a quote or an asterisk or something like that in the middle of the program name, that could very well cause you problems when it gets passed into the SQL.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: query error (A2002)

    Post your DB (after converting it to A2000 if you can) taking out any vital data and we can all have a look.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query error (A2002)

    I deleted, deleted and deleted some more trying to get my db down to a "postable" size here and can't. Even after I eliminated all the forms, reports, and modules as well as any tables or queries not directly tied to the problem query and then cut over 80% if the data out of the tables I could only get it down to 160K zipped.

    In the process I discovered that if I "unshow" one field in the query grid (an amount field with an average function) then the query works with all the programs. If someone would be willing to take a look at the set up and suggest an alternate structure for the query, I'd be more than willing to email the db to them.

    I'm posting the SQL here for the query when I eliminate the amount field:
    SELECT tblProgObj.ProgObjID, tlkpObj.ObjName, Sum(Abs([Approved])) AS NumApproved, Sum(Abs([Achieved])) AS NumAchieved, [NumAchieved]/[NumApproved] AS PercAchieved, tlkpUnits.Unit, qStandard.Standard, tlkpProg.ProgramName
    FROM tlkpUnits INNER JOIN ((tblConsumers INNER JOIN (tlkpProg INNER JOIN tblConsProg ON tlkpProg.ProgramID = tblConsProg.ProgramID) ON tblConsumers.ConsumerID = tblConsProg.ConsumerID) INNER JOIN (tlkpObj INNER JOIN (qsubObjSumDates INNER JOIN ((qStandard INNER JOIN tblProgObj ON qStandard.ProgObjID = tblProgObj.ProgObjID) INNER JOIN tblConsProgObj ON (tblProgObj.ProgObjID = tblConsProgObj.ProgObjID) AND (tblProgObj.ProgObjID = tblConsProgObj.ProgObjID)) ON qsubObjSumDates.ConsProgID = tblConsProgObj.ConsProgID) ON (tlkpObj.ObjID = tblProgObj.ObjID) AND (tlkpObj.ObjID = tblProgObj.ObjID)) ON tblConsProg.ConsProgID = tblConsProgObj.ConsProgID) ON tlkpUnits.UnitID = tblProgObj.UnitID
    GROUP BY tblProgObj.ProgObjID, tlkpObj.ObjName, tlkpUnits.Unit, qStandard.Standard, tlkpProg.ProgramName
    HAVING (((tlkpProg.ProgramName)=[enter program]))
    ORDER BY tblProgObj.ProgObjID;

    Tell me what you think,
    esw

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query error (A2002)

    I assume you have compacted the database before zipping it. You could try saving the database in Access 97 format - Access 97 databases are smaller than their Access 2000 or 2002 equivalents. Since you only need to keep 4 tables and 1 query and just enough rcords to demonstrate the problem, it should be possible to get the zipped size below 100 KB.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: query error (A2002)

    Email it, my profile has my email address.
    What Hans has said is correct, it should not be very large at all.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query error (A2002)

    Saved it to A97. Wow, what a difference. Reduced from 160K to 62K. Here it is. The file name is the name of the problematic query.

    esw
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query error (A2002)

    I thought it was probably something in the program names, so I double checked that I was entering them exactly as they were in the table: Work Services, Community Employment, Respite, Case Management, Supported Community Living, Vocational Evaluation, and Work Adjustment.

    The two that work are Respite and Supported Community Living. Go figure?!

    Any thoughts? If you were having the problem what would be your next step?

    Thanks for the help.

    Elizabeth

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query error (A2002)

    In the database you posted, the query doesn't have the field with Avg and it functions correctly.
    But the query you posted at the start of the thread contains Avg(tblConsProgObj.Amount) AS AvgOfAmount and this won't work, whatever the program name is, because tblConsProgObj.Amount is a text field - at least in the version you posted.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query error (A2002)

    Duh? Why didn't I see that! I changed the text field to number and we were off and running without a hitch. Thank you, thank you, thank you, you are a sanity saver.

    esw

Posting Permissions

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