Forum Discussion

gilbert7's avatar
gilbert7
Copper Contributor
May 04, 2020

Unable to run the queries in Ms access

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 ?

access
Access Web Database
  • George Hepworth's avatar
    George Hepworth
    Steel Contributor
    May 04, 2020

    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. 

  • George Hepworth's avatar
    George Hepworth
    Steel Contributor
    May 04, 2020

    gilbert7 Try removing the HAVING clause. 

     

    You have this:

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

    Is ST_ID really a text string?  

  • Matteo's avatar
    Matteo
    Brass Contributor
    Nov 21, 2020

    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.

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Nov 21, 2020

      Matteo 

      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:

Share

Resources