use the result of a query in a calculated field (without VBA)

[easyit]

Hi,

I have a query the holds a singe value, an exchange rate. I would like to have a second query that uses this result to calculate the proper value. So the calculated field would becom:

Sales FOBk: [tbl_pim_itemlist]![Sales FOB]*[q_exchangerate]![ExchangeRate]

where tbl_pim_itemlist is a linked table, and the query q_exchangerate is not.

If I use the result as I would do with a table, I get a prompt for a parameter. I'm not sure if it is possible to solve like this, can anybody shed some light on this?

BTW
I know how to solve this in VBA, but it would involve a lot of queries to be altered.

EasyIT

"Do you think that’s air you're breathing?

 

[Golom]

Can you post the full SQL rather than just the definition of one field and also the SQL in which you want to use this result? Also, what is it prompting you for exactly?

 

[easyit]

...it is prompting for q_exchangerate!ExchangeRate.

I tried to prevent the full sql, there are a lot fields. I reduced it somewhat:

SELECT [tbl_pim_itemlist]![Sales FOB]*[q_exchangerate]![ExchangeRate] AS [Sales FOBk], tbl_pim_itemlist.MasterKey, tbl_pim_itemlist.[Sales FOB], tbl_ipt_data_received.Searchname
FROM (tbl_pim_itemlist LEFT JOIN tbl_ipt_data_received ON tbl_pim_itemlist.Masterkey = tbl_ipt_data_received.Masterkey) LEFT JOIN tbl_production_data_sheet_received ON tbl_pim_itemlist.Masterkey = tbl_production_data_sheet_received.MasterKey
WHERE (((tbl_ipt_data_received.Searchname)=Eval("[Forms]![frm_Main]![cmbCustomer].column(0)")) AND ((tbl_pim_itemlist.[Season Code])=Eval("[Forms]![frm_Main]![cmbSeason].column(1)")) AND ((tbl_ipt_data_received.FC)=Eval("[Forms]![frm_Main]![cmbFCCycles].column(1)")));

EasyIT

"Do you think that’s air you're breathing?

 

[Golom]

It is prompting for that because q_exchangerate does not appear in your FROM clause so it's assumed to be a parameter.

Try it this way

SELECT 
P.[Sales FOB] * (SELECT ExchangeRate FROM q_exchangerate) AS [Sales FOBk], 
P.MasterKey, P.[Sales FOB], R.Searchname

FROM ((tbl_pim_itemlist As P 
      LEFT JOIN tbl_ipt_data_received As R ON P.Masterkey = R.Masterkey) 
      LEFT JOIN tbl_production_data_sheet_received As D ON P.Masterkey = D.MasterKey)

WHERE (((R.Searchname)   = Eval("[Forms]![frm_Main]![cmbCustomer].column(0)")) 
  AND ((P.[Season Code]) = Eval("[Forms]![frm_Main]![cmbSeason].column(1)")) 
  AND ((R.FC)            = Eval("[Forms]![frm_Main]![cmbFCCycles].column(1)")));

Which will work only if the query returns exactly one row and one column.

 

[easyit]

Golom,

Thank you, that works excellent.

Maarten





EasyIT

"Do you think that’s air you're breathing?