Unable to run the queries in Ms access

%3CLINGO-SUB%20id%3D%22lingo-sub-1357325%22%20slang%3D%22en-US%22%3EUnable%20to%20run%20the%20queries%20in%20Ms%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20Gilbert%20Fernandes.%20I%20am%20facing%20difficulties%20in%20running%20the%20queries%20in%20access%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20three%20tables%3A%20OrderTemp%20%2C%20OPS%24OMAUTH_OMA_WAREHOUSE_ORDER%20and%20Tbl_SERVICE_ORDER%3C%2FP%3E%3CP%3EThis%20is%20part%20of%20automation%20to%20input%20data%20in%20the%20form%20which%20involve%20these%20three%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20query%2C%20to%20fetch%20the%20data%20and%20update%20in%20Order%20Temp%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20query%20attached%20in%20notepad%20which%20use%20to%20update%20the%20remaining%20details%20like%20miles%20and%20weight%20of%20the%20order%20in%20Temp%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20i%20run%20the%20second%20query%2C%20i%20am%20getting%20datatype%20mismatch%20error%20in%20expression.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20Tbl_SERVICE_ORDER.SITE_ID%2C%20OrderTemp.ST_ID%2C%20Tbl_SERVICE_ORDER.REFERENCE1%2C%20Tbl_SERVICE_ORDER.REFERENCE2%2C%20Tbl_SERVICE_ORDER.REFERENCE3%2C%20Tbl_SERVICE_ORDER.REFERENCE4%2C%20Tbl_SERVICE_ORDER.REFERENCE5%2C%20Tbl_SERVICE_ORDER.REFERENCE7%2C%20%0ATbl_SERVICE_ORDER.REFERENCE8%2C%20IIf(%5BREFERENCE3%5D-0%26gt%3B30%2C%5BREFERENCE3%5D-0-30%2C0)%2BIIf(%5BREFERENCE4%5D-0%26gt%3B30%2C%5BREFERENCE4%5D-0-30%2C0)%20AS%20mileDiff%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BREFERENCE7%5D-0%26gt%3B50%2C%5BREFERENCE7%5D-50)%2CIIf(%5BREFERENCE7%5D-0%26gt%3B100%2C%0A%5BREFERENCE7%5D-100))%20AS%20wtDiff%2C%20IIf(IIf(%5BREFERENCE3%5D-0%26gt%3B30%2C%5BREFERENCE3%5D-0-30%2C0)%2BIIf(%5BREFERENCE4%5D-0%26gt%3B30%2C%5BREFERENCE4%5D-0-30%2C0)%26gt%3B0%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C16%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C13%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2C24%2C0)))%2C0)-0%20AS%20mileLineID%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C11%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C10%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C9%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B10%2C8%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D10%2C7%2C0)))))%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C5%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C4%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C3%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B10%2C2%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D10%2C1%2C0)))))%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C14%2C0)))%20AS%20wtlineID%2C%20IIf(IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BREFERENCE7%5D-0%26gt%3B50%2C%5BREFERENCE7%5D-50)%2CIIf(%5BREFERENCE7%5D-0%26gt%3B100%2C%5BREFERENCE7%5D-100))%26gt%3B0%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C12%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2C6%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C15%2C0))))%20AS%20addwtLineID%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%3D''%2C0%2C%0AIIf(Left(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%2C2)%3D'AA'%2C1000%2CIIf(Left(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%2C2)%3D'AS'%2C150%2CIIf(Left(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%2C2)%3D'DL'%2C1062%2CIIf(Left(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%2C2)%3D'UA'%2C1000%2C%0AIIf(Left(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%2C2)%3D'WN'%2C637%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%3D'AMERICAN'%2C1000%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%3D'SOUTHWEST'%2C637%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%3D'DELTA'%2C1062%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D%3D'UNITED'%2C1000))))))))))%20AS%20airline%2C%20IIf(Len(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D)%26gt%3B0%2C42%2C'')%20AS%20airlineChargeID%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C4.26%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C37.82%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C37.82%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C29.13%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D25%2C23.91%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B10%2C22.82%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D10%2C20.70%2C0))))))%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C33.60%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C33.60%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C24.91%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B25%2C19.69%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D25%2C18.60%2C0)))))%2C0)))%20AS%20baseCharge%2C%20%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BmileDiff%5D%26gt%3B0%2C0.19*%5BmileDiff%5D%2C0)%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2CIIf(%5BmileDiff%5D%26gt%3B0%2C0.33*%5BmileDiff%5D%2C0)%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2C%0AIIf(%5BmileDiff%5D%26gt%3B0%2C0.33*%5BmileDiff%5D%2C0))))%20AS%20mileDiffCharge%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BwtDiff%5D%26gt%3B0%2C0.04*%5BwtDiff%5D%2C0)%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2CIIf(%5BwtDiff%5D%26gt%3B0%2C0.4*%5BwtDiff%5D%2C0)%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2CIIf(%5BwtDiff%5D%26gt%3B0%2C0.4*%5BwtDiff%5D%2C0))))%20AS%20wtDiffCharge%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BwtDiff%5D%26gt%3B100%2CIIf(%5BmileDiff%5D%26gt%3B369%2C%0ARound((0.79*(%5BmileDiff%5D%2B30))%2F29%2C0)%2BRound((1.01*%5BmileDiff%5D%2B200)%2F29%2C0)%2CRound((1.01*%5BmileDiff%5D%2B200)%2F29%2C0)))%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C%0AIIf(%5BwtDiff%5D%26gt%3B50%2C14%2C0)%2C0))%20AS%20addLaborCharge%2C%20%5BOPS%24OMAUTH_OMA_WAREHOUSE_ORDER%5D.EXTERNORDERKEY%20AS%20WHORDERID%20Into%20temp%0AFROM%20(OrderTemp%20INNER%20JOIN%20Tbl_SERVICE_ORDER%20ON%20OrderTemp.WH_ORDER_ID%20%3D%20Tbl_SERVICE_ORDER.REFERENCE1)%20INNER%20JOIN%20%5BOPS%24OMAUTH_OMA_WAREHOUSE_ORDER%5D%20ON%20Tbl_SERVICE_ORDER.REFERENCE1%20%3D%20%5BOPS%24OMAUTH_OMA_WAREHOUSE_ORDER%5D.ORDERKEY%0AGROUP%20BY%20Tbl_SERVICE_ORDER.SITE_ID%2C%20OrderTemp.ST_ID%2C%20Tbl_SERVICE_ORDER.REFERENCE1%2C%20Tbl_SERVICE_ORDER.REFERENCE2%2C%20Tbl_SERVICE_ORDER.REFERENCE3%2C%20Tbl_SERVICE_ORDER.REFERENCE4%2C%20Tbl_SERVICE_ORDER.REFERENCE5%2C%20Tbl_SERVICE_ORDER.REFERENCE7%2C%20%0ATbl_SERVICE_ORDER.REFERENCE8%2C%20IIf(%5BREFERENCE3%5D-0%26gt%3B30%2C%5BREFERENCE3%5D-0-30%2C0)%2BIIf(%5BREFERENCE4%5D-0%26gt%3B30%2C%5BREFERENCE4%5D-0-30%2C0)%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BREFERENCE7%5D-0%26gt%3B50%2C%5BREFERENCE7%5D-50)%2CIIf(%5BREFERENCE7%5D-0%26gt%3B100%2C%0A%5BREFERENCE7%5D-100))%2C%20IIf(IIf(%5BREFERENCE3%5D-0%26gt%3B30%2C%5BREFERENCE3%5D-0-30%2C0)%2BIIf(%5BREFERENCE4%5D-0%26gt%3B30%2C%5BREFERENCE4%5D-0-30%2C0)%26gt%3B0%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C16%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C13%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2C24%2C0)))%2C0)-0%2C%20IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C11%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C10%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C9%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B10%2C8%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D10%2C7%2C0)))))%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D101%2C5%2C%0AIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D70%2C4%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B%3D50%2C3%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26gt%3B10%2C2%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE7%5D-0%26lt%3B%3D10%2C1%2C0)))))%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C14%2C0)))%2C%20IIf(IIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2CIIf(%5BREFERENCE7%5D-0%26gt%3B50%2C%5BREFERENCE7%5D-50)%2CIIf(%5BREFERENCE7%5D-0%26gt%3B100%2C%5BREFERENCE7%5D-100))%26gt%3B0%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'D2D'%2C12%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'C2C'%2C6%2CIIf(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE5%5D%3D'DRIVE'%2C15%2C0))))%2C%20IIf(Len(%5BTbl_SERVICE_ORDER%5D.%5BREFERENCE8%5D)%26gt%3B0%2C42%2C'')%2C%20%5BOPS%24OMAUTH_OMA_WAREHOUSE_ORDER%5D.EXTERNORDERKEY%0AHAVING%20(((OrderTemp.ST_ID)%3D%22SKY%22))%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20on%20this%20and%20resolve%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1357325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357586%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20run%20the%20queries%20in%20Ms%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646497%22%20target%3D%22_blank%22%3E%40gilbert7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20but%20that's%20a%20really%20hairy%20query%2C%20sort%20of%20intimidating%20to%20try%20to%20parse%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20in%20general%2C%20make%20sure%20all%20of%20those%20parameters%20are%20properly%20delimited%20for%20the%20datatype%20involved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENumbers%20require%20NO%20delimiter.%3C%2FP%3E%3CP%3EStrings%20of%20text%20require%20the%20text%20delimiter%3A%20%22%3C%2FP%3E%3CP%3EDates%20require%20the%20date%20delimiter%3A%20%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInspect%20each%20of%20your%20parameters%20in%20turn%2C%20comparing%20the%20syntax%20to%20the%20underlying%20datatype.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357769%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20run%20the%20queries%20in%20Ms%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646497%22%20target%3D%22_blank%22%3E%40gilbert7%3C%2FA%3E%26nbsp%3BTry%20removing%20the%20HAVING%20clause.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20this%3A%3C%2FP%3E%3CPRE%3EHAVING%20(((OrderTemp.ST_ID)%3D%22SKY%22))%3B%3C%2FPRE%3E%3CP%3EIs%20ST_ID%20really%20a%20text%20string%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1920106%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20run%20the%20queries%20in%20Ms%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1920106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646497%22%20target%3D%22_blank%22%3E%40gilbert7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20practices%20of%20simplifying%20these%20type%20of%20complexity%20is%20to%20use%20Calculated%20Field%20in%20a%20table%2C%20specifically%20for%20simple%20calculations%3B%20also%2C%20it%20supports%20the%20IIF%20statement.%20That%20will%20eliminate%20creating%20a%20query%20for%20simple%20math%20results.%3C%2FP%3E%3CP%3EFor%20following%20statment%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIIf(%5BREFERENCE3%5D-0%26gt%3B30%2C%5BREFERENCE3%5D-0-30%2C0)%2BIIf(%5BREFERENCE4%5D-0%26gt%3B30%2C%5BREFERENCE4%5D-0-30%2C0)%20AS%20%3CFONT%20color%3D%22%23000000%22%3EmileDiff%3C%2FFONT%3E%3C%2FP%3E%3CP%3ECreate%20a%20field%2C%20use%20the%20%3CFONT%20color%3D%22%230000FF%22%3EmileDiff%3C%2FFONT%3E%20as%20field%20name%2C%20simply%20copy%20paste%20the%20formula%20in%20the%20field%20definition.%20Later%2C%20you%20can%20use%20the%20field%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3EmileDiff%3C%2FFONT%3E%20in%20another%20calculated%20column%3B%3C%2FP%3E%3CP%3E%2CIIf(%3CFONT%20color%3D%22%230000FF%22%3EmileDiff%3C%2FFONT%3E%3CFONT%20color%3D%22%23333333%22%3E%26gt%3B0%3C%2FFONT%3E%3CSPAN%3E%2CIIf(%5BREFERENCE5%5D%3D%22DRIVE%22%2C16%2CIIf(%5BREFERENCE5%5D%3D%22D2D%22....%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFYI%2C%20calculated%20field%20does%20not%20support%20Large%20text.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1920562%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20run%20the%20queries%20in%20Ms%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1920562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F838440%22%20target%3D%22_blank%22%3E%40Metehan68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20probably%20avoid%20calling%20calculated%20fields%20in%20tables%20%22a%20best%20practice%22.%20Otherwise%2C%20this%20could%20work.%3C%2FP%3E%3CP%3EOne%20of%20the%20dangers%20of%20terms%20like%20%22Best%22%20is%20that%20they%20tend%20to%20imply%20a%20consensus%20that%20simply%20doesn't%2C%20and%20can't%20exist%20in%20the%20real%20world.%20Absolutes%20are%20%3CSTRONG%3E%3CEM%3Enever%3C%2FEM%3E%20%3C%2FSTRONG%3Egood.%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2Fimages%2Femoticons%2Flaugh_40x40.gif%22%20alt%3D%22%3Alol%3A%22%20title%3D%22%3Alol%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi guys,

 

I am Gilbert Fernandes. I am facing difficulties in running the queries in access database.

 

There are three tables: OrderTemp , OPS$OMAUTH_OMA_WAREHOUSE_ORDER and Tbl_SERVICE_ORDER

This is part of automation to input data in the form which involve these three tables.

 

First query, to fetch the data and update in Order Temp

 

Second query attached in notepad which use to update the remaining details like miles and weight of the order in Temp table

 

when i run the second query, i am getting datatype mismatch error in expression.

 

 

 

 

SELECT Tbl_SERVICE_ORDER.SITE_ID, OrderTemp.ST_ID, Tbl_SERVICE_ORDER.REFERENCE1, Tbl_SERVICE_ORDER.REFERENCE2, Tbl_SERVICE_ORDER.REFERENCE3, Tbl_SERVICE_ORDER.REFERENCE4, Tbl_SERVICE_ORDER.REFERENCE5, Tbl_SERVICE_ORDER.REFERENCE7, 
Tbl_SERVICE_ORDER.REFERENCE8, IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0) AS mileDiff, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([REFERENCE7]-0>50,[REFERENCE7]-50),IIf([REFERENCE7]-0>100,
[REFERENCE7]-100)) AS wtDiff, IIf(IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0)>0,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',16,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',13,
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',24,0))),0)-0 AS mileLineID, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,11,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,10,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,9,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>10,8,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=10,7,0))))),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,5,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,4,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,3,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>10,2,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=10,1,0))))),
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',14,0))) AS wtlineID, IIf(IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([REFERENCE7]-0>50,[REFERENCE7]-50),IIf([REFERENCE7]-0>100,[REFERENCE7]-100))>0,
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',12,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',6,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',15,0)))) AS addwtLineID, IIf([Tbl_SERVICE_ORDER].[REFERENCE8]='',0,
IIf(Left([Tbl_SERVICE_ORDER].[REFERENCE8],2)='AA',1000,IIf(Left([Tbl_SERVICE_ORDER].[REFERENCE8],2)='AS',150,IIf(Left([Tbl_SERVICE_ORDER].[REFERENCE8],2)='DL',1062,IIf(Left([Tbl_SERVICE_ORDER].[REFERENCE8],2)='UA',1000,
IIf(Left([Tbl_SERVICE_ORDER].[REFERENCE8],2)='WN',637,IIf([Tbl_SERVICE_ORDER].[REFERENCE8]='AMERICAN',1000,IIf([Tbl_SERVICE_ORDER].[REFERENCE8]='SOUTHWEST',637,IIf([Tbl_SERVICE_ORDER].[REFERENCE8]='DELTA',1062,
IIf([Tbl_SERVICE_ORDER].[REFERENCE8]='UNITED',1000)))))))))) AS airline, IIf(Len([Tbl_SERVICE_ORDER].[REFERENCE8])>0,42,'') AS airlineChargeID, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',4.26,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,37.82,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,37.82,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,29.13,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=25,23.91,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>10,22.82,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=10,20.70,0)))))),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,33.60,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,33.60,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,24.91,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>25,19.69,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=25,18.60,0))))),0))) AS baseCharge, 
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([mileDiff]>0,0.19*[mileDiff],0),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',IIf([mileDiff]>0,0.33*[mileDiff],0),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',
IIf([mileDiff]>0,0.33*[mileDiff],0)))) AS mileDiffCharge, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([wtDiff]>0,0.04*[wtDiff],0),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',IIf([wtDiff]>0,0.4*[wtDiff],0),
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',IIf([wtDiff]>0,0.4*[wtDiff],0)))) AS wtDiffCharge, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([wtDiff]>100,IIf([mileDiff]>369,
Round((0.79*([mileDiff]+30))/29,0)+Round((1.01*[mileDiff]+200)/29,0),Round((1.01*[mileDiff]+200)/29,0))),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',
IIf([wtDiff]>50,14,0),0)) AS addLaborCharge, [OPS$OMAUTH_OMA_WAREHOUSE_ORDER].EXTERNORDERKEY AS WHORDERID Into temp
FROM (OrderTemp INNER JOIN Tbl_SERVICE_ORDER ON OrderTemp.WH_ORDER_ID = Tbl_SERVICE_ORDER.REFERENCE1) INNER JOIN [OPS$OMAUTH_OMA_WAREHOUSE_ORDER] ON Tbl_SERVICE_ORDER.REFERENCE1 = [OPS$OMAUTH_OMA_WAREHOUSE_ORDER].ORDERKEY
GROUP BY Tbl_SERVICE_ORDER.SITE_ID, OrderTemp.ST_ID, Tbl_SERVICE_ORDER.REFERENCE1, Tbl_SERVICE_ORDER.REFERENCE2, Tbl_SERVICE_ORDER.REFERENCE3, Tbl_SERVICE_ORDER.REFERENCE4, Tbl_SERVICE_ORDER.REFERENCE5, Tbl_SERVICE_ORDER.REFERENCE7, 
Tbl_SERVICE_ORDER.REFERENCE8, IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0), IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([REFERENCE7]-0>50,[REFERENCE7]-50),IIf([REFERENCE7]-0>100,
[REFERENCE7]-100)), IIf(IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0)>0,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',16,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',13,
IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',24,0))),0)-0, IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,11,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,10,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,9,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>10,8,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=10,7,0))))),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=101,5,
IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=70,4,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>=50,3,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0>10,2,IIf([Tbl_SERVICE_ORDER].[REFERENCE7]-0<=10,1,0))))),IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',14,0))), IIf(IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',IIf([REFERENCE7]-0>50,[REFERENCE7]-50),IIf([REFERENCE7]-0>100,[REFERENCE7]-100))>0,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='D2D',12,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='C2C',6,IIf([Tbl_SERVICE_ORDER].[REFERENCE5]='DRIVE',15,0)))), IIf(Len([Tbl_SERVICE_ORDER].[REFERENCE8])>0,42,''), [OPS$OMAUTH_OMA_WAREHOUSE_ORDER].EXTERNORDERKEY
HAVING (((OrderTemp.ST_ID)="SKY"));

 

 

 

 

Can anyone help me on this and resolve ?

4 Replies
Highlighted

@gilbert7 

 

Sorry, but that's a really hairy query, sort of intimidating to try to parse out.

 

So, in general, make sure all of those parameters are properly delimited for the datatype involved.

 

Numbers require NO delimiter.

Strings of text require the text delimiter: "

Dates require the date delimiter: #

 

Inspect each of your parameters in turn, comparing the syntax to the underlying datatype. 

Highlighted

@gilbert7 Try removing the HAVING clause. 

 

You have this:

HAVING (((OrderTemp.ST_ID)="SKY"));

Is ST_ID really a text string?  

Highlighted

@gilbert7 

Best practices of simplifying these type of complexity is to use Calculated Field in a table, specifically for simple calculations; also, it supports the IIF statement. That will eliminate creating a query for simple math results.

For following statment; 

IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0) AS mileDiff

Create a field, use the mileDiff as field name, simply copy paste the formula in the field definition. Later, you can use the field mileDiff in another calculated column;

,IIf(mileDiff>0,IIf([REFERENCE5]="DRIVE",16,IIf([REFERENCE5]="D2D"....

FYI, calculated field does not support Large text.

Highlighted

@Metehan68 

I would probably avoid calling calculated fields in tables "a best practice". Otherwise, this could work.

One of the dangers of terms like "Best" is that they tend to imply a consensus that simply doesn't, and can't exist in the real world. Absolutes are never good. :lol: