Unable to run the queries in Ms access

[gilbert7]

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 ?

[#2]

@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. 

[#3]

@gilbert7 Try removing the HAVING clause. 

You have this:

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

Is ST_ID really a text string?  

[#4]

@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.

[#5]

@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.