Solved Convert this excel formula to work in query

[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", "")

 

[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).

 

[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.

 

[CJ_London]

@Pat Hartman - you have a extra )

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

 

[Number11]

@Pat Hartman - you have a extra )

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

STILL DOES NOT WORK

 

[Gasman]

23 posts in and all we get is 'It does not work'

Upload a sample DB so someone can get it to work for you.