query field based on form calculation

[Bart]

I have a calculated field in a form using the following:

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

I need to perform the same calculation in a query and used:

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

But I get errors in alll records except the ones where
[SC]="731"

What am I doing wrong?

thanks,
Bart

 

[Bill Crawford]

HI:

Sounds like you have alot of records where neither condition is met

neither [SC]="731" nor [Provider type]=1

Is the [Provider type] field present in the underlying table or subquery?

 

[bart]

[Provider type] is indeed in the underlying table. I'm
showing a "#Error" in all fields other than those with [SC]
="731" , including those where [Provider type]=1. I don't
understand why this IIf statement works in the form field
but won't work here. Is there a better way to do this?

thanks,
bart

-----Original Message-----
HI:

Sounds like you have alot of records where neither condition is met

neither [SC]="731" nor [Provider type]=1

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

I have a calculated field in a form using the following:

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

I need to perform the same calculation in a query and used:

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

But I get errors in alll records except the ones where
[SC]="731"

What am I doing wrong?

thanks,
Bart

.

 

[Bill Crawford]

HI:

Are you using the Group By criteria in your query?

[Provider type] is indeed in the underlying table. I'm
showing a "#Error" in all fields other than those with [SC]
="731" , including those where [Provider type]=1. I don't
understand why this IIf statement works in the form field
but won't work here. Is there a better way to do this?

thanks,
bart

-----Original Message-----
HI:

Sounds like you have alot of records where neither condition is met

neither [SC]="731" nor [Provider type]=1

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

I have a calculated field in a form using the following:

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

I need to perform the same calculation in a query and used:

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

But I get errors in alll records except the ones where
[SC]="731"

What am I doing wrong?

thanks,
Bart

.

 

[Guest]

No, I am not using an "group by" criteria in my query.
Should I be?

-----Original Message-----
HI:

Are you using the Group By criteria in your query?

[Provider type] is indeed in the underlying table. I'm
showing a "#Error" in all fields other than those with [SC]
="731" , including those where [Provider type]=1. I don't
understand why this IIf statement works in the form field
but won't work here. Is there a better way to do this?

thanks,
bart

-----Original Message-----
HI:

Sounds like you have alot of records where neither condition is met

neither [SC]="731" nor [Provider type]=1

Expr1: IIf([SC]="731" Or [Provider type]=1,[Payrate]* [Units],[Payrate]*[Units]*1.1155)>
I have a calculated field in a form using the following:

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

I need to perform the same calculation in a query and used:

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

But I get errors in alll records except the ones where
[SC]="731"

What am I doing wrong?

thanks,
Bart


.

.

 

[Bill Crawford]

Why would you be using the expression in your form and in your query?
Wouldn't that mess things up? Try using grouping in your query along with
that expression and run the query to see the results.

No, I am not using an "group by" criteria in my query.
Should I be?

-----Original Message-----
HI:

Are you using the Group By criteria in your query?

[Provider type] is indeed in the underlying table. I'm
showing a "#Error" in all fields other than those with [SC]
="731" , including those where [Provider type]=1. I don't
understand why this IIf statement works in the form field
but won't work here. Is there a better way to do this?

thanks,
bart
-----Original Message-----
HI:

Sounds like you have alot of records where neither
condition is met

neither [SC]="731" nor [Provider type]=1

Expr1: IIf([SC]="731" Or [Provider type]=1,[Payrate]*
[Units],[Payrate]*[Units]*1.1155)>
message
I have a calculated field in a form using the following:

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

I need to perform the same calculation in a query and
used:

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

But I get errors in alll records except the ones where
[SC]="731"

What am I doing wrong?

thanks,
Bart


.

.