Sum of calculated field from a query

[Guest]

I am working on a database that accepts and calculates wage records. I have
to differentiate between hourly, setrate, and piecerate wages. I have set up
a query that calculates everything perfectly, and all of that information
shows up in my report. However, when I try to sum each employee's wage
records for the week for each rate, I get "Data type mismatch in criteria
expression." when I try to switch to print preview.

As an example, this is my expression in my query to figure the hourly wages:
IIf([rate value]=0,[Hourly Rate]*[count (hrs or pcs)],"")
This is what gives me the aforementioned error in the report when put into
the =sum(). If I simply put [Hourly Rate]*[count (hrs or pcs)], it works,
but of course gives me an incorrect total. I've tried variations such as
IIf([rate value]=0,[Hourly Rate]*Sum([count (hrs or pcs)]),""), but it also
gives me the incorrect total (it figures an hourly rate based on all of the
hrs and pcs instead of only the ones at the correct rate value).

If it is helpful in determining what I'm doing, my other two expressions are:
setwage: IIf([rate value]>0 And [rate]=".60/hour",[rate value]*[count (hrs
or pcs)],"")
prwage: IIf([rate value]>0 And [rate]="piecerate",[rate value]*[count (hrs
or pcs)],"")

Any suggestions on getting each rate to calculate would be greatly
appreciated. Thanks in advance for your time.

 

[Duane Hookom]

An IIf() expression should return the same data type for both the true and
false part. You return either a number or a zero length string. Try
something like:
IIf([rate value]=0,[Hourly Rate]*[count (hrs or pcs)],0)

 

[Guest]

Perfect! Thank you very much for your assistance.

An IIf() expression should return the same data type for both the true and
false part. You return either a number or a zero length string. Try
something like:
IIf([rate value]=0,[Hourly Rate]*[count (hrs or pcs)],0)


--
Duane Hookom
MS Access MVP

I am working on a database that accepts and calculates wage records. I
have
to differentiate between hourly, setrate, and piecerate wages. I have set
up
a query that calculates everything perfectly, and all of that information
shows up in my report. However, when I try to sum each employee's wage
records for the week for each rate, I get "Data type mismatch in criteria
expression." when I try to switch to print preview.

As an example, this is my expression in my query to figure the hourly
wages:
IIf([rate value]=0,[Hourly Rate]*[count (hrs or pcs)],"")
This is what gives me the aforementioned error in the report when put into
the =sum(). If I simply put [Hourly Rate]*[count (hrs or pcs)], it works,
but of course gives me an incorrect total. I've tried variations such as
IIf([rate value]=0,[Hourly Rate]*Sum([count (hrs or pcs)]),""), but it
also
gives me the incorrect total (it figures an hourly rate based on all of
the
hrs and pcs instead of only the ones at the correct rate value).

If it is helpful in determining what I'm doing, my other two expressions
are:
setwage: IIf([rate value]>0 And [rate]=".60/hour",[rate value]*[count (hrs
or pcs)],"")
prwage: IIf([rate value]>0 And [rate]="piecerate",[rate value]*[count (hrs
or pcs)],"")

Any suggestions on getting each rate to calculate would be greatly
appreciated. Thanks in advance for your time.