IIF Statement with 2 Criteria

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

Hello:

I am using the following code in a control on a report
footer:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0))

I need the IIF statement to check for another value so I
used the following syntax:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0)OR IIf([tblBids_Status]="Pending-GC Has Job",
[2001],0))

The second statement does not return the correct
information. For 2001, my value should be $198,500 but
the 2nd statement returns a value of -($2)

Any comments or suggestions would be appreciated.

Thanks
Brennan
 
Hi.
Change the IIF statement to:
=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded" OR
Click to expand...
([tblBids_Status]="Pending-GC Has Job",[2001],0)
Hope this helps
Fons
-----Original Message-----
Hello:

I am using the following code in a control on a report
footer:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0))

I need the IIF statement to check for another value so I
used the following syntax:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0)OR IIf([tblBids_Status]="Pending-GC Has Job",
[2001],0))

The second statement does not return the correct
information. For 2001, my value should be $198,500 but
the 2nd statement returns a value of -($2)

Any comments or suggestions would be appreciated.

Thanks
Brennan
.
Click to expand...
 
Brennan,

Thought I would jump in as I noticed a slight typo with a rogue ( in
Fons's otherwise correct advice.
=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded" OR
[tblBids_Status]="Pending-GC Has Job",[2001],0)

Another way it could be done...
=Sum(IIf([tblBids_Status] In("Pending-GC Not Awarded","Pending-GC Has
Job"),[2001],0)

--
Steve Schapel, Microsoft Access MVP


Fons said:
Hi.
Change the IIF statement to:
=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded" OR
Click to expand...

([tblBids_Status]="Pending-GC Has Job",[2001],0)
Hope this helps
Fons
Click to expand...
 
Thanks for your help. I tried the code you supplied and
I am getting the following error

Syntax error in query expression 'First([>>=Sum(IIf
([tblBids_Status]="Pending-GC Not Awarded" OR
([tblBids_Status]="Pending-GC Has Job",[2001],0)])'

Any ideas

Thanks
Brennan



-----Original Message-----
Hi.
Change the IIF statement to:
=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded" OR
Click to expand...
([tblBids_Status]="Pending-GC Has Job",[2001],0)
Hope this helps
Fons
-----Original Message-----
Hello:

I am using the following code in a control on a report
footer:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0))

I need the IIF statement to check for another value so I
used the following syntax:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0)OR IIf([tblBids_Status]="Pending-GC Has Job",
[2001],0))

The second statement does not return the correct
information. For 2001, my value should be $198,500 but
the 2nd statement returns a value of -($2)

Any comments or suggestions would be appreciated.

Thanks
Brennan
.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Hello:

I am using the following code in a control on a report
footer:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0))

I need the IIF statement to check for another value so I
used the following syntax:

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],0)OR IIf([tblBids_Status]="Pending-GC Has Job",
[2001],0))

The second statement does not return the correct
information. For 2001, my value should be $198,500 but
the 2nd statement returns a value of -($2)

Any comments or suggestions would be appreciated.

Thanks
Brennan
.
YOU NEED TO PUT YOUR SECOND CRITERIA AS THE NEGATIVE PART
Click to expand...
OF THE FIRST IF/THEN STATEMENT INSTEAD OF USING "OR"

=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded",
[2001],IIf([tblBids_Status]="Pending-GC Has Job",
[2001],0)))
 
Brennan,

Don't know whether you have this sorted out yet, but I now notice that I
left off the closing ) in both my suggested expressions. Sorry. Should
be...
=Sum(IIf([tblBids_Status]="Pending-GC Not Awarded" OR
[tblBids_Status]="Pending-GC Has Job",[2001],0))
=Sum(IIf([tblBids_Status] In("Pending-GC Not Awarded","Pending-GC Has
Job"),[2001],0))
 
Back
Top