query field based on form calculation

  • Thread starter Thread starter Bart
  • Start date Start date
B

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
 
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?
 
[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
Click to expand...


.
Click to expand...
 
HI:

Are you using the Group By criteria in your query?

bart said:
[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
Click to expand...


.
Click to expand...
Click to expand...
 
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


.
Click to expand...
Click to expand...


.
Click to expand...
 
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


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top