using a IIF statement in a query

[spence]

I have a calcuated field in a form that uses an IIF
statement and now I need make the same calculation in a
query. I thought I could just use the same IIF statement,
but when I do I get #Error in most records. Here's the
statment:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.115)

The error is showing up in all fields except those where
[SC]="731"

This works perfectly on the form field so what do I need
to do differently to make it work here?

thanks,
spence

 

[Tonster]

How about this:

MyCalcField: IIf([SC]="731" Or [Provider type]=1,[Payrate]*
[Units], >[Payrate]*[Units]*1.115)

You need to define the name of the field (MyCalcField)
followed by : and eliminate the equals sign. This should
work.

The Tonster

 

[spence]

I used your suggested syntax in my query:

MyCalcField: IIF([SC]="731" Or [Provider type]=1,[Payrate]*
[Units],[Payrate]*[Units]*1.115)

I still get an error in all records of the field except
those where [SC]="731"

This is driving me batty because I don't see any reason
it's not working. Any other ideas about what might be
going wrong here?

thanks..

-----Original Message-----
How about this:

MyCalcField: IIf([SC]="731" Or [Provider type]=1,[Payrate] *
[Units], >[Payrate]*[Units]*1.115)

You need to define the name of the field (MyCalcField)
followed by : and eliminate the equals sign. This should
work.

The Tonster

-----Original Message-----
I have a calcuated field in a form that uses an IIF
statement and now I need make the same calculation in a
query. I thought I could just use the same IIF statement,
but when I do I get #Error in most records. Here's the
statment:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.115)

The error is showing up in all fields except those where
[SC]="731"

This works perfectly on the form field so what do I need
to do differently to make it work here?

thanks,
spence
.

.

 

[None]

Could it be your that SC is a numeric field and not a text field?
=IIf([SC]=731 Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.115)

 

[spence]

This screenshot probably wasn't what you had in mind, but hopefully it
will help: http://home.comcast.net/~r.j.spence/accessquery.html

thanks.

-----Original Message-----
Post relevant Table Structure and the SQL String of your
Query.

Van T. Dinh
MVP (Access)

-----Original Message-----
I have a calcuated field in a form that uses an IIF
statement and now I need make the same calculation in a
query. I thought I could just use the same IIF statement,
but when I do I get #Error in most records. Here's the
statment:

=IIf([SC]="731" Or [Provider type]=1,[Payrate]*[Units],
[Payrate]*[Units]*1.115)

The error is showing up in all fields except those where
[SC]="731"

This works perfectly on the form field so what do I need
to do differently to make it work here?

thanks,
spence
.

.

 

[Van T. Dinh]

Not quite. The main thing I was looking for:

* Whether [SC] is a Text Field or a Numeric Field.

* Whether you have Null as possible values for [Payrate] and [Units]. If
it is possible to have Null value, then you should use the Nz() function to
convert Null to zero before multiplications.