By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,158 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,158 IT Pros & Developers. It's quick & easy.

SQL nightmare

P: 15
I am trying to convert an application writtten in Access to vb.net. Alot of the SQL statements are long. Can someone take a look at the following Access SQL statemnt and tell me what SQL statement would look like? This statement works in Access, but the INSERT INTO and FROM tables are the same???
BTW,, A load number looks like this "########-A" or "########-B" etc.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Statements ( MachineID, ShipDate, InvoicedDate, OutgoingLoadNum, ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4, CustomerName, IsStatement )
  2. SELECT DISTINCT 
  3. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="A",DMin('MachineID','Statements')-1,
  4. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="B",DMin("[MachineID]","[Statements]")-2,
  5. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="C",DMin("[MachineID]","[Statements]")-3,
  6. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="D",DMin("[MachineID]","[Statements]")-4,
  7. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="E",DMin("[MachineID]","[Statements]")-5,
  8. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="F",DMin("[MachineID]","[Statements]")-6,
  9. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="G",DMin("[MachineID]","[Statements]")-7,
  10. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="H",DMin("[MachineID]","[Statements]")-8,
  11. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="I",DMin("[MachineID]","[Statements]")-9))))))))) 
  12. AS MachID, Statements.ShipDate, Statements.InvoicedDate, Statements.OutgoingLoadNum, Statements.ShipAdd1, Statements.ShipAdd2, Statements.ShipCity, Statements.ShipState, Statements.ShipZip, Statements.ShipZipPlus4, Statements.BillAdd1, Statements.BillAdd2, Statements.BillCity, Statements.BillState, Statements.BillZip, Statements.BillZipPlus4, Statements.CustomerName, -1 AS IsStatemnt
  13. FROM Statements;
Jul 19 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
I am trying to convert an application writtten in Access to vb.net. Alot of the SQL statements are long. Can someone take a look at the following Access SQL statemnt and tell me what SQL statement would look like? This statement works in Access, but the INSERT INTO and FROM tables are the same???
BTW,, A load number looks like this "########-A" or "########-B" etc.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Statements ( MachineID, ShipDate, InvoicedDate, OutgoingLoadNum, ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4, CustomerName, IsStatement )
  2. SELECT DISTINCT 
  3. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="A",DMin('MachineID','Statements')-1,
  4. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="B",DMin("[MachineID]","[Statements]")-2,
  5. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="C",DMin("[MachineID]","[Statements]")-3,
  6. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="D",DMin("[MachineID]","[Statements]")-4,
  7. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="E",DMin("[MachineID]","[Statements]")-5,
  8. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="F",DMin("[MachineID]","[Statements]")-6,
  9. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="G",DMin("[MachineID]","[Statements]")-7,
  10. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="H",DMin("[MachineID]","[Statements]")-8,
  11. IIf(Mid([OutgoingLoadNum],InStr(1,[OutgoingLoadNum],"-")+1)="I",DMin("[MachineID]","[Statements]")-9))))))))) 
  12. AS MachID, Statements.ShipDate, Statements.InvoicedDate, Statements.OutgoingLoadNum, Statements.ShipAdd1, Statements.ShipAdd2, Statements.ShipCity, Statements.ShipState, Statements.ShipZip, Statements.ShipZipPlus4, Statements.BillAdd1, Statements.BillAdd2, Statements.BillCity, Statements.BillState, Statements.BillZip, Statements.BillZipPlus4, Statements.CustomerName, -1 AS IsStatemnt
  13. FROM Statements;
This nested IIF statements are checking for the character after the first "-" sign.
When "A" the minimum MachineID from the table Statements is corrected with -1, when "B" with -2, etc.

It would have been shorter to use the corretce ascii value of these characters :-)

Nic;o)
Aug 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.