Using Espresions in criteria

B

Bob

I am doing a query that has the following expression in it.
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)

The feild [Net_Tot_Pay_USD] the field tot_voy_pay are the rusults from an
expression in the same query... However when I do a criteria such as <>0 I
am being asked for paramaters for the same fields... How do I get around
this... Below is the SQL for the query..

Thanks for your help and sorry for the double post.

SELECT F4F_SubStudioDetails.Date, studios.epass_account,
F4F_SubStudioDetails.Studio, F4F_SubStudioDetails.Pvt_Girls,
F4F_SubStudioDetails.Pvt_Guys, F4F_SubStudioDetails.Pvt_Tot,
F4F_SubStudioDetails.Ded_Girls, F4F_SubStudioDetails.Ded_Guys,
F4F_SubStudioDetails.Ded_Tot, F4F_SubStudioDetails.Net_Girls,
F4F_SubStudioDetails.Misc4, F4F_SubStudioDetails.Misc4_Des,
F4F_SubStudioDetails.Misc3, F4F_SubStudioDetails.Misc3_Des,
F4F_SubStudioDetails.Misc2_Des, F4F_SubStudioDetails.Misc2,
F4F_SubStudioDetails.Misc1, F4F_SubStudioDetails.Misc1_Des,
F4F_SubStudioDetails.Bonuses, -[F4F_SubStudioDetails]![Violations] AS
Violations, F4F_SubStudioDetails.Tips, F4F_SubStudioDetails.Grp_Tot,
F4F_SubStudioDetails.Grp_Guys, F4F_SubStudioDetails.Grp_Girls,
F4F_SubStudioDetails.Voy_Tot, F4F_SubStudioDetails.Voy_Guys,
F4F_SubStudioDetails.Voy_Girls, F4F_SubStudioDetails.Net_Tot,
F4F_SubStudioDetails.Net_Guys, studios.F4F_Spy_Pay, [Voy_Tot]*[F4F_Spy_Pay]
AS tot_voy_pay,
IIf([Net_Tot]>=[studios]![F4F_rate4_min],[studios]![F4F_rate4_val],IIf([Net_Tot]>=[studios]![F4F_rate3_min],[studios]![F4F_rate3_val],IIf([Net_Tot]>=[studios]![F4F_rate2_min],[studios]![F4F_rate2_val],[studios]![F4F_rate1_val])))
AS USD_rate, [Net_Tot]*[USD_rate] AS Net_Tot_Pay_USD, sevice_payouts.service,
sevice_payouts.pvt_min, sevice_payouts.voy_min, [Net_Tot]*[pvt_min] AS
USD_pvt_gross, [Voy_Tot]*[voy_min] AS USD_voy_gross,
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)
AS USD_Report_Tot
FROM sevice_payouts, studios INNER JOIN F4F_SubStudioDetails ON
studios.Studio = F4F_SubStudioDetails.Studio
WHERE (((F4F_SubStudioDetails.Date)=#8/5/2007#) AND
((sevice_payouts.service)="f4f") AND
((Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0))<>0))
ORDER BY F4F_SubStudioDetails.Studio;
 
K

KARL DEWEY

Fields [Net_Tot_Pay_USD] and [tot_voy_pay] are known as alias. In MOST
cases you can not use the alias in the same query that generated it. You
need to re-use the same calculation instead of the alias.

--
KARL DEWEY
Build a little - Test a little


Bob said:
I am doing a query that has the following expression in it.
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)

The feild [Net_Tot_Pay_USD] the field tot_voy_pay are the rusults from an
expression in the same query... However when I do a criteria such as <>0 I
am being asked for paramaters for the same fields... How do I get around
this... Below is the SQL for the query..

Thanks for your help and sorry for the double post.

SELECT F4F_SubStudioDetails.Date, studios.epass_account,
F4F_SubStudioDetails.Studio, F4F_SubStudioDetails.Pvt_Girls,
F4F_SubStudioDetails.Pvt_Guys, F4F_SubStudioDetails.Pvt_Tot,
F4F_SubStudioDetails.Ded_Girls, F4F_SubStudioDetails.Ded_Guys,
F4F_SubStudioDetails.Ded_Tot, F4F_SubStudioDetails.Net_Girls,
F4F_SubStudioDetails.Misc4, F4F_SubStudioDetails.Misc4_Des,
F4F_SubStudioDetails.Misc3, F4F_SubStudioDetails.Misc3_Des,
F4F_SubStudioDetails.Misc2_Des, F4F_SubStudioDetails.Misc2,
F4F_SubStudioDetails.Misc1, F4F_SubStudioDetails.Misc1_Des,
F4F_SubStudioDetails.Bonuses, -[F4F_SubStudioDetails]![Violations] AS
Violations, F4F_SubStudioDetails.Tips, F4F_SubStudioDetails.Grp_Tot,
F4F_SubStudioDetails.Grp_Guys, F4F_SubStudioDetails.Grp_Girls,
F4F_SubStudioDetails.Voy_Tot, F4F_SubStudioDetails.Voy_Guys,
F4F_SubStudioDetails.Voy_Girls, F4F_SubStudioDetails.Net_Tot,
F4F_SubStudioDetails.Net_Guys, studios.F4F_Spy_Pay, [Voy_Tot]*[F4F_Spy_Pay]
AS tot_voy_pay,
IIf([Net_Tot]>=[studios]![F4F_rate4_min],[studios]![F4F_rate4_val],IIf([Net_Tot]>=[studios]![F4F_rate3_min],[studios]![F4F_rate3_val],IIf([Net_Tot]>=[studios]![F4F_rate2_min],[studios]![F4F_rate2_val],[studios]![F4F_rate1_val])))
AS USD_rate, [Net_Tot]*[USD_rate] AS Net_Tot_Pay_USD, sevice_payouts.service,
sevice_payouts.pvt_min, sevice_payouts.voy_min, [Net_Tot]*[pvt_min] AS
USD_pvt_gross, [Voy_Tot]*[voy_min] AS USD_voy_gross,
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)
AS USD_Report_Tot
FROM sevice_payouts, studios INNER JOIN F4F_SubStudioDetails ON
studios.Studio = F4F_SubStudioDetails.Studio
WHERE (((F4F_SubStudioDetails.Date)=#8/5/2007#) AND
((sevice_payouts.service)="f4f") AND
((Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0))<>0))
ORDER BY F4F_SubStudioDetails.Studio;
Click to expand...
 
B

Bob

Thank Karl I appreciate you clear and concise help...

Bob


KARL DEWEY said:
Fields [Net_Tot_Pay_USD] and [tot_voy_pay] are known as alias. In MOST
cases you can not use the alias in the same query that generated it. You
need to re-use the same calculation instead of the alias.

--
KARL DEWEY
Build a little - Test a little


Bob said:
I am doing a query that has the following expression in it.
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)

The feild [Net_Tot_Pay_USD] the field tot_voy_pay are the rusults from an
expression in the same query... However when I do a criteria such as <>0 I
am being asked for paramaters for the same fields... How do I get around
this... Below is the SQL for the query..

Thanks for your help and sorry for the double post.

SELECT F4F_SubStudioDetails.Date, studios.epass_account,
F4F_SubStudioDetails.Studio, F4F_SubStudioDetails.Pvt_Girls,
F4F_SubStudioDetails.Pvt_Guys, F4F_SubStudioDetails.Pvt_Tot,
F4F_SubStudioDetails.Ded_Girls, F4F_SubStudioDetails.Ded_Guys,
F4F_SubStudioDetails.Ded_Tot, F4F_SubStudioDetails.Net_Girls,
F4F_SubStudioDetails.Misc4, F4F_SubStudioDetails.Misc4_Des,
F4F_SubStudioDetails.Misc3, F4F_SubStudioDetails.Misc3_Des,
F4F_SubStudioDetails.Misc2_Des, F4F_SubStudioDetails.Misc2,
F4F_SubStudioDetails.Misc1, F4F_SubStudioDetails.Misc1_Des,
F4F_SubStudioDetails.Bonuses, -[F4F_SubStudioDetails]![Violations] AS
Violations, F4F_SubStudioDetails.Tips, F4F_SubStudioDetails.Grp_Tot,
F4F_SubStudioDetails.Grp_Guys, F4F_SubStudioDetails.Grp_Girls,
F4F_SubStudioDetails.Voy_Tot, F4F_SubStudioDetails.Voy_Guys,
F4F_SubStudioDetails.Voy_Girls, F4F_SubStudioDetails.Net_Tot,
F4F_SubStudioDetails.Net_Guys, studios.F4F_Spy_Pay, [Voy_Tot]*[F4F_Spy_Pay]
AS tot_voy_pay,
IIf([Net_Tot]>=[studios]![F4F_rate4_min],[studios]![F4F_rate4_val],IIf([Net_Tot]>=[studios]![F4F_rate3_min],[studios]![F4F_rate3_val],IIf([Net_Tot]>=[studios]![F4F_rate2_min],[studios]![F4F_rate2_val],[studios]![F4F_rate1_val])))
AS USD_rate, [Net_Tot]*[USD_rate] AS Net_Tot_Pay_USD, sevice_payouts.service,
sevice_payouts.pvt_min, sevice_payouts.voy_min, [Net_Tot]*[pvt_min] AS
USD_pvt_gross, [Voy_Tot]*[voy_min] AS USD_voy_gross,
Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0)
AS USD_Report_Tot
FROM sevice_payouts, studios INNER JOIN F4F_SubStudioDetails ON
studios.Studio = F4F_SubStudioDetails.Studio
WHERE (((F4F_SubStudioDetails.Date)=#8/5/2007#) AND
((sevice_payouts.service)="f4f") AND
((Nz([Net_Tot_Pay_USD],0)+Nz([tot_voy_pay],0)+Nz([Tips],0)+Nz([Violations],0)+Nz([Bonuses],0)+Nz([Misc1],0)+Nz([Misc2],0)+Nz([Misc3],0)+Nz([Misc4],0))<>0))
ORDER BY F4F_SubStudioDetails.Studio;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Criteria 7

Top