If Statements

[k11ngy]

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

 

[Dirk Goldgar]

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

 

[Jeff Boyce]

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]

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

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)

 

[k11ngy]

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]

?where

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

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

 

[Dirk Goldgar]

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.

 

[k11ngy]

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

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)

 

[k11ngy]

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

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)

 

[John W. Vinson]

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.

 

[Dirk Goldgar]

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

 

[yjx]

fhdchnbm,njghyyyd

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)