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?
Thanks!
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?
Thanks!
Code:
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;