Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2019, 04:48 AM   #16
Adamfm
Newly Registered User
 
Join Date: Jul 2019
Posts: 12
Thanks: 6
Thanked 0 Times in 0 Posts
Adamfm is on a distinguished road
Re: Error on IIF

Hi CJ,

"Successful" is Boolean so I have changed the table to number and now Yes=0 and No is -1, is that what you wanted me to do?

So now will use IIf([Successful]=-1,"1","0")

then will look at

PercentUnsuccessful: Sum([Failed])/Sum([Boarded])*100

Thanks...

Will let you know if I can get it to work.

Adamfm is offline   Reply With Quote
Old 08-12-2019, 04:52 AM   #17
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,350
Thanks: 40
Thanked 3,671 Times in 3,539 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Error on IIF

No. There is no need to change anything I haven’t mentioned. I suggested removing formatting not changing anything
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-12-2019, 06:18 AM   #18
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,350
Thanks: 40
Thanked 3,671 Times in 3,539 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Error on IIF

just realised I have the formula the wrong way round. It should be

PercentUnsuccessful: Sum([Successful]+1)/sum(abs(Successful]))*100

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-12-2019, 06:31 AM   #19
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Error on IIF

@ OP,

So you can better understand what CJ_London was posting about,
ANY time you pass a value inside quotes or any time you tell something to be returned in quotes, ACCESS treats it as a text value, not numeric.

As such, when you have
Quote:
Yes: IIf([Successful]="Yes","1","0")
ACCESS us looking for the literal text "Yes" inside your [Successful] field and returning either the character "1" or the Character "0". To get ACCESS to check the field [Successful] for the value of Yes (value -1) and return a number you would need to change it to be
Code:
Yes: IIF([Successful] = True, 1, 0)
This is one piece that I think was giving you some issues.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Adamfm (08-12-2019)
Old 08-12-2019, 07:21 AM   #20
Adamfm
Newly Registered User
 
Join Date: Jul 2019
Posts: 12
Thanks: 6
Thanked 0 Times in 0 Posts
Adamfm is on a distinguished road
Re: Error on IIF

OK CJ brilliant that worked thanks, got there in the end!

One small thing I tried to use the Rnd function to 2dp within the equation but it doesn't like it, can you advise please?

PercentageFail: Sum(([Fail])/([Success])*100)
Adamfm is offline   Reply With Quote
Old 08-12-2019, 07:25 AM   #21
Adamfm
Newly Registered User
 
Join Date: Jul 2019
Posts: 12
Thanks: 6
Thanked 0 Times in 0 Posts
Adamfm is on a distinguished road
Re: Error on IIF

Hi Mark,

Yes I understand now, even though it was showing 1 and 0 they were text rather than numbers. I actually changed the table values from Yes/No to numbers which was the easiest thing for me.

Thanks for the advice!
Adamfm is offline   Reply With Quote
Old 08-12-2019, 07:37 AM   #22
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Error on IIF

Adamfm,

Are you actually looking for the percentage failed or the ratio or fail to success?

Percentage failed would be failed/success+failed. This means if you have 20 failed and 80 success, you'd have a total of 100 and your percentage would be 20%. Ratio would be 20 to 80 or 25%.

Mark_ is offline   Reply With Quote
Old 08-12-2019, 08:56 AM   #23
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Error on IIF

if you are using Grouped by query, use this as "Expression"
Code:
PercentageFail: Sum(IIf([successful]=0,1,0))/Sum(1)*100

PercentageSuccess: Sum(IIf([successful],1,0))/Sum(1)*100

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error "Run-time error '1004': General ODBC Error D4WNO General 1 07-19-2012 07:42 AM
Error 3734 (share mode error) appearing in error guy- Modules & VBA 2 09-28-2011 02:02 AM
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM
ACC2000: Error Message: Error Accessing File. Network Connection May Have Been Lost. jenvandiver General 0 10-30-2002 09:19 AM




All times are GMT -8. The time now is 07:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World