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

gotti777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.