Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IIF Statement from Microsoft Acccess

Posted on 2009-07-16
24
Medium Priority
?
194 Views
Last Modified: 2012-05-07
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
Comment
Question by:gotti777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
24 Comments
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24870025
Do you want your output to just be the % of items shipped on time?
0
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24870112
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
 

Author Comment

by:gotti777
ID: 24870139
yes .. but i guess i want 2 expression one with the number of on time deliveries and one with the %  
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:gotti777
ID: 24870195
Syntax Error (missing operator) in query expression 'SUM(IIF([DateShipped] <= [DatePromised], 1, 0))/COUNT(Invoice.SONumber)
invoice.writtenby'.
0
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24870371
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
 

Author Comment

by:gotti777
ID: 24870622
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24870841
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
 

Author Comment

by:gotti777
ID: 24872007
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24872528
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24872536
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
 

Author Comment

by:gotti777
ID: 24872726
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24872778
No the expression you want is the Expr/total records. Ie. Total on_time divided by total invoices.
0
 

Author Comment

by:gotti777
ID: 24873063
So how do i right that in builder?
0
 

Author Comment

by:gotti777
ID: 24873234
sorry for taking up alot of your time should i start a new thread?
0
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24873257
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24873295
No, that's alright.
0
 

Author Comment

by:gotti777
ID: 24873617
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
 
LVL 15

Accepted Solution

by:
dirknibleck earned 2000 total points
ID: 24873655
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24873687
If you click here....

You will probably get rid of that error message
access.jpg
0
 

Author Comment

by:gotti777
ID: 24873781
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24873857
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
 

Author Comment

by:gotti777
ID: 24873872
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
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24873910
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
 

Author Comment

by:gotti777
ID: 24873976
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question