Solved

IIF Statement from Microsoft Acccess

Posted on 2009-07-16
24
179 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now