If Statements

Yes, probably wasnt that clear

I have

[AON Level 1] [AON Level 2] either of these must be a Pass or Exempt

The 3rd field [AON Portfolio] must achieve a PASS and a pass or exemption
from any of the above to obtain full award (Else "")

Apologies for being vague

Kind regards

"Jeff Boyce" wrote:

> Access is not a "spreadsheet on steroids", so the references to Excel
> "cells" won't work in Access.
>
> "How" depends on "what" ... what are the field names in Access?
>
> Where are you trying to doing this? A form? A query? A table?
>
> More info, please...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "k11ngy" <k11ngy.DeleteThis@discussions.microsoft.com> wrote in message
> news:FD23F52F-C5A7-45DF-8B74-B3D6AD490F71@microsoft.com...
> >I recently asked the question in Excel and received good reply
> >
> > =IF(AND(OR(C2="Pass",C2="Exempt"),OR(D2="Pass",D2="Exempt")),"FULL
> > AWARD","")
> >
> > I was wondering if anyone knew the formula in Access? Tried several times
> > but to no avail
> > As above, I needed
> >
> > if A1 and A2 =pass or Exempt, Full Award, else "" or Null? (Not sure which
> > one)?
> >
> > Thanks for help
> >
> > Regards
> >
> > Steve
>
>
>
 >> Stay informed about: If Statements 

?where

Regards

Jeff Boyce
Microsoft Office/Access MVP

"k11ngy" <k11ngy.TakeThisOut@discussions.microsoft.com> wrote in message
news:45AD73D9-DC23-4656-ABC3-FD6544D3DA13@microsoft.com...
> Yes, probably wasnt that clear
>
> I have
>
> [AON Level 1] [AON Level 2] either of these must be a Pass or Exempt
>
> The 3rd field [AON Portfolio] must achieve a PASS and a pass or exemption
> from any of the above to obtain full award (Else "")
>
> Apologies for being vague
>
> Kind regards
>
> "Jeff Boyce" wrote:
>
>> Access is not a "spreadsheet on steroids", so the references to Excel
>> "cells" won't work in Access.
>>
>> "How" depends on "what" ... what are the field names in Access?
>>
>> Where are you trying to doing this? A form? A query? A table?
>>
>> More info, please...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "k11ngy" <k11ngy.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:FD23F52F-C5A7-45DF-8B74-B3D6AD490F71@microsoft.com...
>> >I recently asked the question in Excel and received good reply
>> >
>> > =IF(AND(OR(C2="Pass",C2="Exempt"),OR(D2="Pass",D2="Exempt")),"FULL
>> > AWARD","")
>> >
>> > I was wondering if anyone knew the formula in Access? Tried several
>> > times
>> > but to no avail
>> > As above, I needed
>> >
>> > if A1 and A2 =pass or Exempt, Full Award, else "" or Null? (Not sure
>> > which
>> > one)?
>> >
>> > Thanks for help
>> >
>> > Regards
>> >
>> > Steve
>>
>>
>>
 >> Stay informed about: If Statements 

"k11ngy" <k11ngy.RemoveThis@discussions.microsoft.com> wrote in message
news:FD23F52F-C5A7-45DF-8B74-B3D6AD490F71@microsoft.com...
>I recently asked the question in Excel and received good reply
>
> =IF(AND(OR(C2="Pass",C2="Exempt"),OR(D2="Pass",D2="Exempt")),"FULL
> AWARD","")
>
> I was wondering if anyone knew the formula in Access? Tried several times
> but to no avail
> As above, I needed
>
> if A1 and A2 =pass or Exempt, Full Award, else "" or Null? (Not sure which
> one)?


Something along the lines of:

=IIf(([A1]="Pass" Or [A1]="Exempt") And ([A2]="Pass" Or [A2]="Exempt"),
"Full Award", "")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 >> Stay informed about: If Statements 

Thanks for help and it does work, thank you, can I ask another as people here
chucked another scenario at me

It would invlove 3 criteria

1. If [A1] or [A2] are pass or exempt and [A3]=Pass or exempt ""Full Award,
else ""

Hope you can help

Kind regards

Steve

"Dirk Goldgar" wrote:

> "k11ngy" <k11ngy RemoveThis @discussions.microsoft.com> wrote in message
> news:FD23F52F-C5A7-45DF-8B74-B3D6AD490F71@microsoft.com...
> >I recently asked the question in Excel and received good reply
> >
> > =IF(AND(OR(C2="Pass",C2="Exempt"),OR(D2="Pass",D2="Exempt")),"FULL
> > AWARD","")
> >
> > I was wondering if anyone knew the formula in Access? Tried several times
> > but to no avail
> > As above, I needed
> >
> > if A1 and A2 =pass or Exempt, Full Award, else "" or Null? (Not sure which
> > one)?
>
>
> Something along the lines of:
>
> =IIf(([A1]="Pass" Or [A1]="Exempt") And ([A2]="Pass" Or [A2]="Exempt"),
> "Full Award", "")
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
 >> Stay informed about: If Statements 

"k11ngy" <k11ngy RemoveThis @discussions.microsoft.com> wrote in message
news:BAB86446-77C5-4C5C-815E-033E0FE77E99@microsoft.com...
> Thanks for help and it does work, thank you, can I ask another as people
> here
> chucked another scenario at me
>
> It would invlove 3 criteria
>
> 1. If [A1] or [A2] are pass or exempt and [A3]=Pass or exempt ""Full
> Award,
> else ""
>
> Hope you can help

Did you try to work it out yourself, based on the example already provided
and the help-file entry for the IIf function? We generally prefer to teach
people to fish. But ...

Let me restate the logic to see if I understand it:

IF (
([A1] = "Pass" OR [A1] = "Exempt")
OR
([A2] = "Pass" OR [A2] = "Exempt")
)
AND
([A3] = "Pass" OR [A3] = "Exempt")
THEN
return "Full Award"
ELSE
return ""

Is that correct? If so, I would first reorder the clauses so that the most
restrictive one comes first:

IF ([A3] = "Pass" OR [A3] = "Exempt")
AND
(
([A1] = "Pass" OR [A1] = "Exempt")
OR
([A2] = "Pass" OR [A2] = "Exempt")
)
THEN
return "Full Award"
ELSE
return ""

Then I'd reassemble them into proper sequence for an IIf expression:

=IIf([A3] = "Pass" OR [A3] = "Exempt") AND (([A1] = "Pass" OR [A1] =
"Exempt") OR ([A2] = "Pass" OR [A2] = "Exempt")), "Full Award", "")

That may be hard to read, since the line is very long and will have been
broken onto multiple lines arbitrarily by the newsreader. I'll restate it,
breaking onto multiple line at logical places:

=IIf([A3] = "Pass" OR [A3] = "Exempt")
AND (([A1] = "Pass" OR [A1] = "Exempt")
OR ([A2] = "Pass" OR [A2] = "Exempt")),
"Full Award",
"")

Note, though, that in most places you could write this, it must actually be
entered all on one line.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 >> Stay informed about: If Statements 

Hi

Thanks for info, I do use the help menus and evry other resource I can find
to try and work things out, need more practice I suppose. I can see the
logic in the formulas, think I have more problems with Brackets and order of
function.

The last one you gave me, I typed in ONE line as and expression and it
claims I have too many arguments?

I will keep tweeking it about to see if I can get it to work

Thanks

Steve

"Dirk Goldgar" wrote:

> "k11ngy" <k11ngy.DeleteThis@discussions.microsoft.com> wrote in message
> news:BAB86446-77C5-4C5C-815E-033E0FE77E99@microsoft.com...
> > Thanks for help and it does work, thank you, can I ask another as people
> > here
> > chucked another scenario at me
> >
> > It would invlove 3 criteria
> >
> > 1. If [A1] or [A2] are pass or exempt and [A3]=Pass or exempt ""Full
> > Award,
> > else ""
> >
> > Hope you can help
>
> Did you try to work it out yourself, based on the example already provided
> and the help-file entry for the IIf function? We generally prefer to teach
> people to fish. But ...
>
> Let me restate the logic to see if I understand it:
>
> IF (
> ([A1] = "Pass" OR [A1] = "Exempt")
> OR
> ([A2] = "Pass" OR [A2] = "Exempt")
> )
> AND
> ([A3] = "Pass" OR [A3] = "Exempt")
> THEN
> return "Full Award"
> ELSE
> return ""
>
> Is that correct? If so, I would first reorder the clauses so that the most
> restrictive one comes first:
>
> IF ([A3] = "Pass" OR [A3] = "Exempt")
> AND
> (
> ([A1] = "Pass" OR [A1] = "Exempt")
> OR
> ([A2] = "Pass" OR [A2] = "Exempt")
> )
> THEN
> return "Full Award"
> ELSE
> return ""
>
> Then I'd reassemble them into proper sequence for an IIf expression:
>
> =IIf([A3] = "Pass" OR [A3] = "Exempt") AND (([A1] = "Pass" OR [A1] =
> "Exempt") OR ([A2] = "Pass" OR [A2] = "Exempt")), "Full Award", "")
>
> That may be hard to read, since the line is very long and will have been
> broken onto multiple lines arbitrarily by the newsreader. I'll restate it,
> breaking onto multiple line at logical places:
>
> =IIf([A3] = "Pass" OR [A3] = "Exempt")
> AND (([A1] = "Pass" OR [A1] = "Exempt")
> OR ([A2] = "Pass" OR [A2] = "Exempt")),
> "Full Award",
> "")
>
> Note, though, that in most places you could write this, it must actually be
> entered all on one line.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
 >> Stay informed about: If Statements 

Thanks so much for help everyone. After some tweeking, I did manage to get
it to work.

That led to:

I used the query for a report and created a count function =count([Results
AON])

It actually counted all the fields, even "Null Fields" I tried to set
criteria to "Not Null" but still shows Null Fields when query is run so not
sure how I combat that one? Will keep investigating

Thanks again

Steve

"Dirk Goldgar" wrote:

> "k11ngy" <k11ngy RemoveThis @discussions.microsoft.com> wrote in message
> news:BAB86446-77C5-4C5C-815E-033E0FE77E99@microsoft.com...
> > Thanks for help and it does work, thank you, can I ask another as people
> > here
> > chucked another scenario at me
> >
> > It would invlove 3 criteria
> >
> > 1. If [A1] or [A2] are pass or exempt and [A3]=Pass or exempt ""Full
> > Award,
> > else ""
> >
> > Hope you can help
>
> Did you try to work it out yourself, based on the example already provided
> and the help-file entry for the IIf function? We generally prefer to teach
> people to fish. But ...
>
> Let me restate the logic to see if I understand it:
>
> IF (
> ([A1] = "Pass" OR [A1] = "Exempt")
> OR
> ([A2] = "Pass" OR [A2] = "Exempt")
> )
> AND
> ([A3] = "Pass" OR [A3] = "Exempt")
> THEN
> return "Full Award"
> ELSE
> return ""
>
> Is that correct? If so, I would first reorder the clauses so that the most
> restrictive one comes first:
>
> IF ([A3] = "Pass" OR [A3] = "Exempt")
> AND
> (
> ([A1] = "Pass" OR [A1] = "Exempt")
> OR
> ([A2] = "Pass" OR [A2] = "Exempt")
> )
> THEN
> return "Full Award"
> ELSE
> return ""
>
> Then I'd reassemble them into proper sequence for an IIf expression:
>
> =IIf([A3] = "Pass" OR [A3] = "Exempt") AND (([A1] = "Pass" OR [A1] =
> "Exempt") OR ([A2] = "Pass" OR [A2] = "Exempt")), "Full Award", "")
>
> That may be hard to read, since the line is very long and will have been
> broken onto multiple lines arbitrarily by the newsreader. I'll restate it,
> breaking onto multiple line at logical places:
>
> =IIf([A3] = "Pass" OR [A3] = "Exempt")
> AND (([A1] = "Pass" OR [A1] = "Exempt")
> OR ([A2] = "Pass" OR [A2] = "Exempt")),
> "Full Award",
> "")
>
> Note, though, that in most places you could write this, it must actually be
> entered all on one line.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
 >> Stay informed about: If Statements 

On Tue, 14 Oct 2008 07:58:20 -0700, k11ngy <k11ngy.RemoveThis@discussions.microsoft.com>
wrote:

>Thanks so much for help everyone. After some tweeking, I did manage to get
>it to work.
>
>That led to:
>
>I used the query for a report and created a count function =count([Results
>AON])
>
>It actually counted all the fields, even "Null Fields" I tried to set
>criteria to "Not Null" but still shows Null Fields when query is run so not
>sure how I combat that one? Will keep investigating

Count doesn't count fields - it counts records. If the record is retrieved
it's a record; it makes no difference if some of the fields are NULL.

--

John W. Vinson [MVP]
 >> Stay informed about: If Statements 

"k11ngy" <k11ngy.DeleteThis@discussions.microsoft.com> wrote in message
news:63BCD69D-630D-4B05-8950-AFD8F62514C3@microsoft.com...
>
> The last one you gave me, I typed in ONE line as and expression and it
> claims I have too many arguments?


Looks like I had my own problem with brackets on that one -- left off a
leading one. Try this:

=IIf(([A3]="Pass" Or [A3]="Exempt") And (([A1]="Pass" Or [A1]="Exempt")
Or ([A2]="Pass" Or [A2]="Exempt")),"Full Award","")


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 >> Stay informed about: If Statements 

fhdchnbm,njghyyyd
"Dirk Goldgar" <dg.TakeThisOut@NOdataSPAMgnostics.com.invalid> дÈëÏûÏ¢ÐÂÎÅ:uP7TqSmLJHA.3080@TK2MSFTNGP06.phx.gbl...
> "k11ngy" <k11ngy.TakeThisOut@discussions.microsoft.com> wrote in message
> news:63BCD69D-630D-4B05-8950-AFD8F62514C3@microsoft.com...
>>
>> The last one you gave me, I typed in ONE line as and expression and it
>> claims I have too many arguments?
>
>
> Looks like I had my own problem with brackets on that one -- left off a
> leading one. Try this:
>
> =IIf(([A3]="Pass" Or [A3]="Exempt") And (([A1]="Pass" Or [A1]="Exempt")
> Or ([A2]="Pass" Or [A2]="Exempt")),"Full Award","")
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
 >> Stay informed about: If Statements