Error on IIF (1 Viewer)

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
Hi,

Trying to add a value of 1 if yes using the following but I'm getting an error in the field when I run the query? Can anyone help please?

Yes: IIf([Successful]="Yes","1","0")

Thanks in advance!
Adam
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,169
if it is a yes/no field:

Yes: IIf([Successful=-1, "1", "0")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
when you say add a value of 1 do you mean the number 1 or the text "1" (you cant add text)

if successful is a boolean and you want the number 1 then all you need is

yes:abs(successful)

also be aware that yes is a reserved word, using it as a field name can cause unexpected errors, often with misleading descriptions
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
Brilliant!

That worked so now I have the two fields (took CJ's advice and removed the Yes)

Boarded: IIf([Successful]=-1,"1","0")
and
Failed: IIf([Successful]=0,"1","0")

So the next thing do is calculate the percentage of failures per flight, which I think I can do.

Thanks very much!!!!
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
OK,

So I've got a sum attached Boarded and Failed image attached
Then tried this:
PercentUnsuccessful: (([Failed]/[Boarded])*100)

Getting #Div/0! - does this have to do with it not being boolean?
 

Attachments

  • Boarded and Failed.jpg
    Boarded and Failed.jpg
    15.5 KB · Views: 56
  • Unsuccessful.jpg
    Unsuccessful.jpg
    30.2 KB · Views: 51

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
see post #3 - you are using text, not numbers and you can't do maths with text
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
Hi CJ, I was hoping this would change it to the boolean value

Boarded: Sum(Abs(IIf([Successful]=-1,"1","0")))
and
Failed: Sum(Abs(IIf([Successful]=0,"1","0")))

but I'm still getting #Div/0! on the following query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,169
I posted the sql query on you other post and remind you that if it is a yes/no field replace it with -1, 0.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
No - booleans are number - you are converting them to text

I'm confused by your requirement since successful appears to already be a boolean

Assuming it is then you have

boarded: sum(abs(successful))
Failed:sum(successful+1)

PercentUnsuccessful: ((sum(successful+1)/sum(abs(successful))*100)

Edit: but you will get a division be zero error if boarded is 0, so you probably need

PercentUnsuccessful: iif(sum(abs(successful)),((sum(successful+1)/sum(abs(successful))*100))
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,169
if I remember:
Code:
select distinct [date], [flight number], 
dcount("1","tableName","[flight number]='" & [flight number] & "' and successful=-1)/dcount("1",""tableName","[flight number]='" & [flight number] & "'")*100 as PercentSuccess, 
 dcount("1","tableName","[flight number]='" & [flight number] & "' and successful=0)/dcount("1",""tableName","[flight number]='" & [flight number] & "'")*100 as PercentFailure from tableName;
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
arelgp thanks but just cannot get that to work? I replaced Yes and No with 1 and 0 as you suggested, thanks.

CJ tried your code

SELECT Qry3TotalPaxBoarded0.Airline, Qry3TotalPaxBoarded0.Airport, Qry3TotalPaxBoarded0.Date, Qry3TotalPaxBoarded0.[Flight Number], IIf(Sum(Abs([Successful])),Sum([Successful]+1)/Sum(Abs([Successful]))*100) AS PercentUnsuccessful
FROM Qry3TotalPaxBoarded0
GROUP BY Qry3TotalPaxBoarded0.Airline, Qry3TotalPaxBoarded0.Airport, Qry3TotalPaxBoarded0.Date, Qry3TotalPaxBoarded0.[Flight Number];

but PercentUnsuccessfull came out as 100 on every row?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
you need to check your values - suggest take out the summing and grouping and display the successful, boarded and failed columns to see what you actually have

successful can only be true or false, so to test the formula, in the immediate window

for boarded
?abs(true)
1
?abs(false)
0

for failed
?true+1
0
?false+1
1

just as a note 'percentunsuccesful' implies failed divided by failed+boarded. You have divided by boarded alone so say 100 seats, 80 boarded, 20 failed - so I would expect to see from your formula 20/80=25 - is that what you want?
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
Hi CJ,

Sorry I have been away for a week.

Coming back to the problem I took away the grouping and summing and used the following which worked fine

Boarded: Abs(IIf([Successful]=-1,"1","0"))
Failed: Abs(IIf([Successful]=0,"1","0"))

Now trying to get the average which is what you mention in your comment "so say 100 seats, 80 boarded, 20 failed - so I would expect to see from your formula 20/80=25 - is that what you want?"

Tried this but it is not working :(
Getting the error Division by zero?

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

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
OK,

So I understand now what the issue is in that Boarded and Failed need to be on the same row in order to calculate the average but at present they are on 2 separate rows (see attachment) which is why I'm getting the Zero value error.

How do I get it so that the boarded and failed are on the same row instead of 2 separate rows?

Thanks in advance.
Adam
 

Attachments

  • Qry.jpg
    Qry.jpg
    62 KB · Views: 56

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
add back your grouping then your percentage would be

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

note your failed and boarded values need to be numeric

at the moment this generates text values - and you cannot apply any maths function to text

IIf([Successful]=-1,"1","0")

from your query snapshot it looks like your successful field is a boolean which is numeric - all you are doing is converting it to text. I strongly advise you take out all formatting from your tables so you can see what you really have.

try this method (in your group by query) which is what I was alluding to

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

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
No. There is no need to change anything I haven’t mentioned. I suggested removing formatting not changing anything
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:47
Joined
Feb 19, 2013
Messages
16,553
just realised I have the formula the wrong way round. It should be

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

Mark_

Longboard on the internet
Local time
Today, 07:47
Joined
Sep 12, 2017
Messages
2,111
@ 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
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.
 

Adamfm

Registered User.
Local time
Today, 14:47
Joined
Jul 24, 2019
Messages
12
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)
 

Users who are viewing this thread

Top Bottom