Convert Access query to SQL

T

taxidermist

I have the following Query from Access that needs to be converted to
SQL. Can anyone out there do that for me? I am lost.

SELECT LREJECTS.SERIAL, "E" AS Code, +[ccorg_1]![BACE1] &
IIf([ccorg_1]![BACE2]="3","3",IIf([LREJECTS]![P]="8" Or [LREJECTS]!
[P]="2" Or [LREJECTS]![P]="6" Or [LREJECTS]![P]="4" Or Mid([LREJECTS]!
[BACE],2,1)="1","1","2")) & [ccorg_1]![BACE3] & Right([LREJECTS]!
[BACE],1) AS Correction, IIf([ccorg]![ORG2]=[ccorg_1]!
[ORG2],"Yes","No") AS Expr1, LREJECTS.CD, Left([mwp],1) AS Expr2,
LREJECTS.MWP, IIf([cc]=hme],"No","Yes") AS Expr3, Left([mwp],4) AS
Expr4, Left([MWP],2) AS Expr5, LREJECTS.HME, LREJECTS.CC,
LREJECTS.BACE, LREJECTS.HME
FROM ((LREJECTS INNER JOIN ccorg ON LREJECTS.CC = ccorg.COSTCENTER)
INNER JOIN ccorg AS ccorg_1 ON LREJECTS.HME = ccorg_1.COSTCENTER) LEFT
JOIN [Exception Summary] ON LREJECTS.BADGE = [Exception Summary].Badge
WHERE (((IIf([ccorg]![ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND
((LREJECTS.CD)="615") AND ((Left([mwp],1))="0") AND
((LREJECTS.MWP)<>"097106002745" And (LREJECTS.MWP)<>"097104002747" And
(LREJECTS.MWP)<>"097101052000" And (LREJECTS.MWP)<>"097101102000" And
(LREJECTS.MWP)<>"097101152000" And (LREJECTS.MWP)<>"097101202000" And
(LREJECTS.MWP)<>"097104002452" And (LREJECTS.MWP)<>"097104002453" And
(LREJECTS.MWP)<>"097106002667") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="640")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453" And (LREJECTS.MWP)<>"097106002667" And
(LREJECTS.MWP)<>"097106002603" And (LREJECTS.MWP)<>"097106002604") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
((LREJECTS.HME)<>"999" And (LREJECTS.HME)<>"136" And
(LREJECTS.HME)<>"164" And (LREJECTS.HME)<>"995") AND (([Exception
Summary].Badge) Is Null)) OR (((IIf([ccorg]![ORG2]=[ccorg_1]!
[ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="616") AND ((Left([mwp],
1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="614")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="621")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="640")
AND ((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([MWP],2))="00")
AND (([Exception Summary].Badge) Is Null))
ORDER BY LREJECTS.SERIAL;
 
D

Douglas J. Steele

By "converted to SQL", I assume you mean you want to be able to run it in
SQL Server.

Try replacing all IIf(condition, value1, value2) with IF condition value1
ELSE value2

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have the following Query from Access that needs to be converted to
SQL. Can anyone out there do that for me? I am lost.

SELECT LREJECTS.SERIAL, "E" AS Code, +[ccorg_1]![BACE1] &
IIf([ccorg_1]![BACE2]="3","3",IIf([LREJECTS]![P]="8" Or [LREJECTS]!
[P]="2" Or [LREJECTS]![P]="6" Or [LREJECTS]![P]="4" Or Mid([LREJECTS]!
[BACE],2,1)="1","1","2")) & [ccorg_1]![BACE3] & Right([LREJECTS]!
[BACE],1) AS Correction, IIf([ccorg]![ORG2]=[ccorg_1]!
[ORG2],"Yes","No") AS Expr1, LREJECTS.CD, Left([mwp],1) AS Expr2,
LREJECTS.MWP, IIf([cc]=hme],"No","Yes") AS Expr3, Left([mwp],4) AS
Expr4, Left([MWP],2) AS Expr5, LREJECTS.HME, LREJECTS.CC,
LREJECTS.BACE, LREJECTS.HME
FROM ((LREJECTS INNER JOIN ccorg ON LREJECTS.CC = ccorg.COSTCENTER)
INNER JOIN ccorg AS ccorg_1 ON LREJECTS.HME = ccorg_1.COSTCENTER) LEFT
JOIN [Exception Summary] ON LREJECTS.BADGE = [Exception Summary].Badge
WHERE (((IIf([ccorg]![ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND
((LREJECTS.CD)="615") AND ((Left([mwp],1))="0") AND
((LREJECTS.MWP)<>"097106002745" And (LREJECTS.MWP)<>"097104002747" And
(LREJECTS.MWP)<>"097101052000" And (LREJECTS.MWP)<>"097101102000" And
(LREJECTS.MWP)<>"097101152000" And (LREJECTS.MWP)<>"097101202000" And
(LREJECTS.MWP)<>"097104002452" And (LREJECTS.MWP)<>"097104002453" And
(LREJECTS.MWP)<>"097106002667") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="640")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453" And (LREJECTS.MWP)<>"097106002667" And
(LREJECTS.MWP)<>"097106002603" And (LREJECTS.MWP)<>"097106002604") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
((LREJECTS.HME)<>"999" And (LREJECTS.HME)<>"136" And
(LREJECTS.HME)<>"164" And (LREJECTS.HME)<>"995") AND (([Exception
Summary].Badge) Is Null)) OR (((IIf([ccorg]![ORG2]=[ccorg_1]!
[ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="616") AND ((Left([mwp],
1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="614")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="621")
AND ((Left([mwp],1))="0") AND ((LREJECTS.MWP)<>"097106002745" And
(LREJECTS.MWP)<>"097104002747" And (LREJECTS.MWP)<>"097101052000" And
(LREJECTS.MWP)<>"097101102000" And (LREJECTS.MWP)<>"097101152000" And
(LREJECTS.MWP)<>"097101202000" And (LREJECTS.MWP)<>"097104002452" And
(LREJECTS.MWP)<>"097104002453") AND
((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([mwp],4))<>"0120" And
(Left([mwp],4))<>"0142" And (Left([mwp],4))<>"0445" And (Left([mwp],
4))<>"0655") AND ((Left([MWP],2))<>"02" And (Left([MWP],2))<>"06") AND
(([Exception Summary].Badge) Is Null)) OR (((IIf([ccorg]!
[ORG2]=[ccorg_1]![ORG2],"Yes","No"))="Yes") AND ((LREJECTS.CD)="640")
AND ((IIf([cc]=[hme],"No","Yes"))="Yes") AND ((Left([MWP],2))="00")
AND (([Exception Summary].Badge) Is Null))
ORDER BY LREJECTS.SERIAL;
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looking for a way to Force Successor Task to next workday IF 'x' amount of time left in workday 0
Check if a date comes before/after a range of dates 0
Union Query SQL 2
Query the same table three times 0
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
How to detect the same substring 5
Access query translate to SQL Server 1
4 #Error Lines in a Query 2

Top