Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015

    Unanswered: Strange Invalid Procedure Call Error

    Hi There!

    I’m working on an (quite messy) access database not made from myself and I get an invalid procedure call in the query below . The behaviour is really strange, at least to a non expert of access as myself.
    Basically the query worked fine in a previous version of the database but after the table “Trees_2eBosinv”, on which this query depends on, has been updated with the inclusion of new records the problem started. When I run the query it works fine, but if I try to sort the field BR4 or FL4 or RT4 access return the invalid procedure call error and this obviously prevent also the other queries that depend on this to work properly returning the same error. This behaviour looks really strange to me since BR1/2/3/4 (and the same for FL and RT) just have to run a different equation on the basis of the value of “([qTreeSpeciesScientific]![Compartment_formula]” that can be 1,2,3 or 4...but seems that the query does not like the number 4 . I tried to run the equations of BR/FL/RT 4 for the other values in the field Compartment_formula and it works for all of them...but the number 4. I also tried to change the number 4 in the source table into a different one (5,6) changing the conditional statement but nothing, it still gives me the same error.
    Anyone can help me?

    SELECT Trees_2eBosinv.Tree_ID, Trees_2eBosinv.IDPlots, qTreeSpeciesScientific.Value, Trees_2eBosinv.Status_tree, Trees_2eBosinv.DBH_mm, Trees_2eBosinv.Height_m, qTreeSpeciesScientific.Compartment_formula, qTreeSpeciesScientific.CC, IIf([qTreeSpeciesScientific]![Compartment_formula]=1,IIf([Trees_2eBosinv]![DBH_mm]<621,(0.021*([Trees_2eBosinv]![DBH_mm]/10)^2.471),(0.021*([Trees_2eBosinv]![DBH_mm]/10)^2.471)),0) AS BR1, IIf([qTreeSpeciesScientific]![Compartment_formula]=2,0.0022*([Trees_2eBosinv]![DBH_mm]/10)^2.9122,0) AS BR2, IIf([qTreeSpeciesScientific]![Compartment_formula]=3,Exp(-2.675+4.42*Log([Trees_2eBosinv]![DBH_mm]/10)-2.784*Log([Trees_2eBosinv]![Height_m])),0) AS BR3, IIf([qTreeSpeciesScientific]![Compartment_formula]=4,Exp(1.16345+1.74592*Log([Trees_2eBosinv]![DBH_mm]/10)-0.94993*[Trees_2eBosinv]![Height_m]/([Trees_2eBosinv]![DBH_mm]/10)+Log(1.102)),0) AS BR4, IIf([qTreeSpeciesScientific]![Compartment_formula]=1,0.375+0.0024*([Trees_2eBosinv]![DBH_mm]/10)^2.517,0) AS FL1, IIf([qTreeSpeciesScientific]![Compartment_formula]=2,0.00445*([Trees_2eBosinv]![DBH_mm]/10)^2.2371,0) AS FL2, IIf([qTreeSpeciesScientific]![Compartment_formula]=3,Exp(-1.349+3.351*Log([Trees_2eBosinv]![DBH_mm]/10)-2.201*Log([Trees_2eBosinv]![Height_m])),0) AS FL3, IIf([qTreeSpeciesScientific]![Compartment_formula]=4,Exp(-1.347+3.351*Log([Trees_2eBosinv]![DBH_mm]/10)-2.201*Log([Trees_2eBosinv]![Height_m])),0) AS FL4, IIf([qTreeSpeciesScientific]![Compartment_formula]=1,IIf([Trees_2eBosinv]![DBH_mm]<200,0.0282*([Trees_2eBosinv]![DBH_mm]/10)^2.39,0.0282*([Trees_2eBosinv]![DBH_mm]/10)^2.39),0) AS RT1, IIf([qTreeSpeciesScientific]![Compartment_formula]=2,0.33989*([Trees_2eBosinv]![DBH_mm]/10)^1.4728,0) AS RT2, IIf([qTreeSpeciesScientific]![Compartment_formula]=3,(0.00002179*([Trees_2eBosinv]![DBH_mm]/10)^2.4209)*1000,0) AS RT3, IIf([qTreeSpeciesScientific]![Compartment_formula]=4,IIf([Trees_2eBosinv]![DBH_mm]<250,1.0554*Exp(-5.37891+2.29211*Log([Trees_2eBosinv]![DBH_mm]/10)),1.0554*Exp(-5.37891+2.29211*Log([Trees_2eBosinv]![DBH_mm]/10))),0) AS RT4
    FROM Trees_2eBosinv INNER JOIN qTreeSpeciesScientific ON Trees_2eBosinv.Species=qTreeSpeciesScientific.ID
    GROUP BY Trees_2eBosinv.Tree_ID, Trees_2eBosinv.IDPlots, qTreeSpeciesScientific.Value, Trees_2eBosinv.Status_tree, Trees_2eBosinv.DBH_mm, Trees_2eBosinv.Height_m, qTreeSpeciesScientific.Compartment_formula, qTreeSpeciesScientific.CC;

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if all that has changed is new data (ie no design changes (changes to column types, deletion of columns) then my first suspicion would be a data issue. and the most likely cause for that is going to be
    ...allowing numeric values to be stored in string/text datatypes, meaning it works as long as there are no alpha characters n the column
    OR have NULLS in your data and are not handling them correctly (use the NZ function to do that)

    but to be honest I don't know, and Im not going to plough god knows how many lines of SQL just to find a potential error

    what you could consider is getting rid of the IIFs and replace with a value pulled from a table (using a join). not only does it make your SQL easier to read it also means that if another compartment formula is required usign a tbael means the user just adds a row to the table

    IIF's are a great tool, but they are precisely that a tool, and the right tool should be used for the job.... just because a hammer can drive a screw into softwood, it doens't mean you should always use a hammer
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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