Dcount: referencing a saved query field in a mathematical expression

[joeyrhyu]

Hi all,

Quick question regarding referencing a saved query field in a
mathematical expression for a dcount function.

I have a saved query called GetTmpTotPayoutFSR with the following
code:

SELECT payee_id, period_id, manager_flg, financial_center, cp_spread
AS spread, annual_goal
FROM WORK_TOT_PAYOUT
WHERE manager_flg=0;

--------------------

This saved query is referenced in the following update statement that
utilizes a dcount:

UPDATE WORK_INDV_MGR_BUMP AS IMB SET tier_1_count =
dcount("[payee_id]","[GetTmpTotPayoutFSR]","financial_center = '" &
imb.financial_center &
"'" & "And period_id = " & imb.period_id &

"And spread >=" & ((annual_goal/12)*(imb.tier_1_threshold_pct/100)) &
"And spread <" & ((annual_goal/12)*(imb.tier_2_threshold_pct/100)));


My problem is that I want to reference the "annual_goal" field from
the saved query "GetTmpTotPayoutFSR," yet this field also exists on
the WORK_INDV_MGR_BUMP field.

I've tried the following:

1)
"And spread >=" & (([GetTmpTotPayoutFSR].[annual_goal]/
12)*(imb.tier_1_threshold_pct/100)) &
"And spread <" & (([GetTmpTotPayoutFSR].[annual_goal]/
12)*(imb.tier_2_threshold_pct/100)));

This doesn't work.

2) I tried giving the annual_goal field in the saved query another
alias such as "AG" and did the following:
"And spread >=" & ((AG/12)*(imb.tier_1_threshold_pct/100)) &
"And spread <" & ((AG/12)*(imb.tier_2_threshold_pct/100)));

This doesn't work as well.

I'm sure that the problem lies within the use of quotes, but I'm not
sure how to fix it. I just recently learned how to use quotes for
text and number fields in the where clause of a dcount/dsum, so
hopefully this will be just as easy!

Thank you!

 

[Guest]

Try:

UPDATE WORK_INDV_MGR_BUMP AS IMB
SET tier_1_count =
dcount
(
"[payee_id]","[GetTmpTotPayoutFSR]", "financial_center = '" &
imb.financial_center &"' And period_id = " & imb.period_id & "And spread >=
((annual_goal/12)*(imb.tier_1_threshold_pct/100)) And spread <
((annual_goal/12)*(imb.tier_2_threshold_pct/100))
);