add sql query value to control source of a textbox

[Cowboy_BeBa]

hey everyone

im hoping theres a simple solution to what i need to do

im working on adding new features to a db i built last year and i need to add a textbox to a report, the textbox should be autocalculated when the report loads. it needs to be the sum of two different fields (on different tables from the one this report is based on, using the key "batchRun")

Ive writtena query to solve this
[SELECT ( sum (tblAddProduction.addPercentage) + sum (tblAddIngredient.adiPercentage))
FROM tblAddProduction, tblAddIngredient
WHERE tblAddProduction.BatchRun=tblAddIngredient.batchRun=[batchRun]]*

*the final [batchRun] in the where statement refers to a text field on the actual report that this sql code will be added to

so far the problem is when i enter this sql into the expression builder for that control it tells me theres malformed GUID constant, can anyone help me out with this one?

cheers,
ben

 

[stopher]

Are you just wanting the sum of two single values? Probably easiest to use a couple of DSum functions.

Another option is to join your new query to the report's query. Then you can add the fields.

If you get stuck then post your database.

hth
Chris

 

[Cowboy_BeBa]

thanks for the advice stopher,

ive converted my sql into a dsum function so that it looks like this

=Nz(DSum("addPercentage", "addProduction", "batchRun="rptPrintBatchSheets.batchRun), 0) + Nz(DSum("adiPercentage", "adiProduction", "batchRun=" rptPrintBatchSheets.batchRun), 0)

rptPrintBatchSheets is the report the function is going into and batchRun is another field on that report containing the primary key... i also figured id add the nz function for items that have no records in the two tables im summing up

the problem is this expression generates a similar error,
"The expression you entered contains invalid syntax.
You may have entered an operand without an operator"

any ideas whats causing this?

 

[stopher]

=Nz(DSum("addPercentage", "addProduction", "batchRun="rptPrintBatchSheets.batchRun), 0) + Nz(DSum("adiPercentage", "adiProduction", "batchRun=" rptPrintBatchSheets.batchRun), 0)

Try:
=Nz(DSum("addPercentage", "addProduction", "batchRun=" & rptPrintBatchSheets.batchRun), 0) + Nz(DSum("adiPercentage", "adiProduction", "batchRun=" & rptPrintBatchSheets.batchRun), 0)

hth

 

[Cowboy_BeBa]

thanks stopher, that did the trick