Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

IIF Statement from Microsoft Acccess

Ok I am trying to create an if statement that returns a yes or a no or some type of false true I have a promised date and a Shipped date.  If ship date is passed promised then its late and i need to return a false so I need to build an expression I assume then a count of items that were on time.  So I can divide that by number of lines to get a percent  
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
gotti777
Asked:
gotti777
  • 13
  • 11
1 Solution
 
dirknibleckCommented:
Do you want your output to just be the % of items shipped on time?
0
 
dirknibleckCommented:
I think this will get you in the right direction.
SELECT SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber)
 
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
 
 
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
 
 
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
 
GROUP BY Invoice.InvoiceType

Open in new window

0
 
gotti777Author Commented:
yes .. but i guess i want 2 expression one with the number of on time deliveries and one with the %  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gotti777Author Commented:
Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber)
invoice.writtenby'.
0
 
dirknibleckCommented:
Ok.  You are doing this in Access, right?

Try:
SELECT IIF([DateShipped] <= [DatePromised], 1, 0)
 
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
 
 
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
 
 
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))

Open in new window

0
 
gotti777Author Commented:
Yes sorry forgot to metion it.

I get the same error.   Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber)
invoice.writtenby'.

i can get the expression to return a 1 or 0  buy this code
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, IIf([DateShipped]<=[DatePromised],1,0) AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
 
dirknibleckCommented:
Ok, so far so good then. I guess it doesn't like my GROUPBY/Summary piece

Try:


SELECT Invoice.InvoiceType, IIF([DateShipped] <= [DatePromised], 1, 0)
 
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
 
 
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
 
 
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))

Open in new window

0
 
gotti777Author Commented:
nope still same error..  got it working like this giving me a 0 or 1 ..  now should i try and figuar out the percent in the query .. I need a chart Graph showing percent should i do the rest in a form and figuar it out there?
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, IIf([DateShipped]<=[DatePromised],1,0) AS Expr1
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
 
dirknibleckCommented:
I can't tell what part it doesn't like. In order to do the count and percentage you want, you're going to need to group the data by something. I assumed that Invoice.InvoiceType would be the same for every record, which would make this possible.

Maybe I'm just missing the as...

Try this:

SELECT SUM(IIF([DateShipped] <= [DatePromised], 1, 0)) as num_on_time, SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber) as percent_on_time
 
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
 
 
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
 
 
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
 
GROUP BY Invoice.InvoiceType

Open in new window

0
 
dirknibleckCommented:
If that doesn't work, try this...
SELECT Invoice.InvoiceType, IIF([DateShipped] <= [DatePromised], 1, 0) as on_time
 
Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped, [DatePromised]-[DateShipped] AS Expr1
 
 
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
 
 
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))

Open in new window

0
 
gotti777Author Commented:
hmm nope i am grouping by INs Rep when i try and create my chart
I do get the number of on time perfect  
shouldnt i try and add an expression now that  saids   sum[Item] / [Expr1]   I like SQL server reporting services so much better,much eaiser I just need to build this metric in access driving me nutz


access.bmp
0
 
dirknibleckCommented:
No the expression you want is the Expr/total records. Ie. Total on_time divided by total invoices.
0
 
gotti777Author Commented:
So how do i right that in builder?
0
 
gotti777Author Commented:
sorry for taking up alot of your time should i start a new thread?
0
 
dirknibleckCommented:
Well, since you have something that's working, let's try this...
SELECT COUNT(Invoice.SONumber) AS [Orders], SUM(IIf([DateShipped]<=[DatePromised],1,0)) AS [OnTime]
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
 
dirknibleckCommented:
No, that's alright.
0
 
gotti777Author Commented:
well kind of confusing cause your code returns one count on all items and all item ontime.  

which i already have in the old code.  returns 1 if good 0 if pass date
 Expr1: IIf([DateShipped]<=[DatePromised],1,0)

if i try added this to a field
 OnTime: Sum(IIf([DateShipped]<=[DatePromised],1,0))
you tried to execute a query that does not include the specified expression"ins rep"as part of an aggreegate function
access.JPG
0
 
dirknibleckCommented:
That was the result I was hoping for. We are trying to get the % of orders placed on time right? This would be the # on time / # total.

So now we can do:
SELECT SUM(IIf([DateShipped]<=[DatePromised],1,0))/COUNT(Invoice.SONumber) AS [PercentOnTime], SUM(IIf([DateShipped]<=[DatePromised],1,0)) AS [OnTime]
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
 
dirknibleckCommented:
If you click here....

You will probably get rid of that error message
access.jpg
0
 
gotti777Author Commented:
Hmm ok well i am confused with your code.. it on the right track it retruns 2 number one ... one ont time and a percent

i need to group INS reps then show there on time then there percent

cant i just do this on a graph
it cant be this hard
0
 
dirknibleckCommented:
Oh, ok, so you want it grouped by ins reps. That's easy. In the query builder - if you have my last code in there - add the INS Reps columns. It should Default to "Group By" and then it will work.

Otherwise you can use this code:


SELECT Invoice.WrittenBy AS [INS Rep], SUM(IIf([DateShipped]<=[DatePromised],1,0))/COUNT(Invoice.SONumber) AS [PercentOnTime], SUM(IIf([DateShipped]<=[DatePromised],1,0)) AS [OnTime]
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long" And ([Invoice Line Detail-View].CustomerName)<>"Jill Boski" And ([Invoice Line Detail-View].CustomerName)<>"Gerry mitchell") AND (([Invoice Line Detail-View].Item)<>"MISCELLANOUS") AND ((Invoice.DateShipped)>=#1/1/2009# And (Invoice.DateShipped)<=#3/31/2009#) AND ((Invoice.InvoiceType)="SHIP"))
 
GROUP BY Invoice.WrittenBy;

Open in new window

0
 
gotti777Author Commented:
this is what mine returns .. i want it to group the ins reps   other line to show me totals and other to show me percent
access2.JPG
0
 
dirknibleckCommented:
Try Taking out all of the columns except for the agent, and the on_time_ness. And see if that looks like what you're expecting.
0
 
gotti777Author Commented:
ok that last code you sent me returned  name number on time and a percent.. i am trying to add one more with total lines but looks good.  now to get it on a graph i will start a new thread for this
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now