Solved Convert this excel formula to work in query (1 Viewer)

[Number11]

Hello

I have this in a spreadsheet that works all fine i want to create this within a Ms Query...


=IF((OR(AC2="yes",AD2="yes",AE2="yes")),"Yes","")

My fields are

BOOKED
DELIVERED
OUTSTANDING

I tried this but doesnt work

=IF((OR([BOOKED]="yes",[DELIVERED]="yes",[OUTSTANDING]="yes")),"Yes","")

 

[Gasman]

I do not believe Access has an OR() function, just the OR keyword ?

 

[strive4peace]

hi @Number11 ,

are the fields defined to be Yes/No in Access? If so

=IIF([field1]=true or [field2]=true or [field3]=true, true, false)

 

[arnelgp]

or simply:

=IIF([BOOKED] + [DELIVERED] + [OUTSTANDING], "Yes", "")

 

[Pat Hartman]

I don't use functions when there are other alternatives.

If you don't know how to create queries, time to fire up the QBE and start.
1. select the table
2. select the columns you want
3. if the fields are YN data type, put true in the criteria line of the first field. Skip down one row and put True in the second. Then down to the third row and True in the third.

The QBE AND's all criteria on the same line and OR's criteria on different lines. If you switch to SQL view, you will see the WHERE clause is

WHERE [BOOKED] = True or [DELIVERED] = True or [OUTSTANDING] = True

buried in a sea of open/close parentheses. Access Looooooooooves parentheses.

 

[arnelgp]

I don't use functions when there are other alternatives.

if you will follow the OP, he is outputting a "Yes" to a column (possibly calculated).

 

[Pat Hartman]

You could be right but the OP never answered the question regarding data type. The concept is the same whether you compare to True or "yes".

 

[CJ_London]

assuming this is a control and the fields are a boolean type then

=[BOOKED] OR [DELIVERED] OR [OUTSTANDING]

and set the format of the control to ;"Yes";""

 

[Number11]

assuming this is a control and the fields are a boolean type then

=[BOOKED] OR [DELIVERED] OR [OUTSTANDING]

and set the format of the control to ;"Yes";""

No this does not work

Test

BOOKED​	DELIVERED​	OUTSTANDING​	Results​
No	No	Yes	-1​
No	Yes	No	-1​

same result even when a yes is present

 

[arnelgp]

see post#1 for your condition, that is Exactly the same as you write it in excel.

 

[Number11]

see post#1 for your condition, that is Exactly the same as you write it in excel.

No does not work as i said in my first post?

 

[Minty]

PMFJI, but that's what you wanted? Any yes in those three fields = yes?

-1 Is Yes in Access.

 

[Number11]

PMFJI, but that's what you wanted? Any yes in those three fields = yes?

-1 Is Yes in Access.

yes for those with a yes i can also seeing the same when all 3 are the no

BOOKED	DELIVERED	OUTSTANDING	Expr1
No	No	No	-1​

 

[CJ_London]

re post #9 - assuming Expr1 is your formula it appears to be working to me - each row has a yes in it and because it is or's it will return true - you just haven't formatted it

;"Yes";""

and what does this mean?

same result even when a yes is present

you will get the same result whenever there is a yes

 

[Number11]

yes f

re post #9 - assuming Expr1 is your formula it appears to be working to me - each row has a yes in it and because it is or's it will return true - you just haven't formatted it

;"Yes";""

and what does this mean?



you will get the same result whenever there is a yes

no its not working see...

yes for those with a yes i can also seeing the same when all 3 are the no

BOOKED	DELIVERED	OUTSTANDING	Expr1
No	No	No	-1

 

[Gasman]

Show your code

 

[GPGeorge]

Hello

I have this in a spreadsheet that works all fine i want to create this within a Ms Query...


=IF((OR(AC2="yes",AD2="yes",AE2="yes")),"Yes","")

My fields are

BOOKED
DELIVERED
OUTSTANDING

I tried this but doesnt work

=IF((OR([BOOKED]="yes",[DELIVERED]="yes",[OUTSTANDING]="yes")),"Yes","")

Perhaps this slipped past. Several previous posters did mention it.

In Access, Boolean fields (those with two possible values, i.e. "True" or "False", "Yes" or "No") are stored in tables as -1 and 0, where 0 is false, or no, and -1 is true, or yes.

In your Access expression, therefore, you do NOT check "yes" or "no", you check -1 or 0.

It's possible that you have stored strings, not Booleans, in which case you could put anything in that field in the table, in addition to "yes" or "no" because a string isn't limited to the Booleans.

So, depending on whether you really do have a Boolean, or merely a string of letters, you have to craft the expression appropriately.

 

[Number11]

Perhaps this slipped past. Several previous posters did mention it.

In Access, Boolean fields (those with two possible values, i.e. "True" or "False", "Yes" or "No") are stored in tables as -1 and 0, where 0 is false, or no, and -1 is true, or yes.

In your Access expression, therefore, you do NOT check "yes" or "no", you check -1 or 0.

It's possible that you have stored strings, not Booleans, in which case you could put anything in that field in the table, in addition to "yes" or "no" because a string isn't limited to the Booleans.

So, depending on whether you really do have a Boolean, or merely a string of letters, you have to craft the expression appropriately.

changed to -1 still doent work

 

[GPGeorge]

changed to -1 still doent work

Please state clearly then, what does it mean to say "...doesn't work" ? Do you get an error, wrong answers, NO answers?

Show us the actual result required.

Show us the actual result returned.

Explain the difference to be sure we all understand the nature of the problem.

Thank you.

 

[Pat Hartman]

We still don't know if you have a text field or a Boolean. But assuming it is text, you were close with your original guess:

=IF((OR([BOOKED]="yes",[DELIVERED]="yes",[OUTSTANDING]="yes")),"Yes","")

should be

=IIF([BOOKED]="yes" OR [DELIVERED]="yes" OR [OUTSTANDING]="yes"),"Yes","")

The IIf() contains three arguments -- IIf(condition, true path, false path)
Any or all of the arguments can be complex. In your case, the condition is compound. You are checking three fields and if any one of them is "yes", then we take the "true path". Otherwise we take the "false path".

The true and false paths can be replaced by entire IIf() statements

IIf(condition, IIf(condition, true path, false path) , false path)
or
IIf(condition, true path, IIf(condition, true path, false path))
or
IIf(condition, IIf(condition, true path, IIf(condition, true path, false path) ) , false path) -- I'm not sure how deeply you can nest but this expression will be very hard for humans to understand so I would recommend creating a UDF which will be easier to code and read.