SQL statements inside a calculated expression

[jerrysmith]

The following expression works on a report.

Me.Text18.ControlSource = "=Int((Sum([qryAllFieldsForReports]![TotalCount]-[qryAllFieldsForReports]![RandomCount])/(Sum([qryAllFieldsForReports]![TotalCount]))*100))"

What I want to know is how to change the query on which the expression is based. I tried to subsitute the following for these parts "[qryAllFieldsForReports]![TotalCount]" and "[qryAllFieldsForReports]![RandomCount]" with no luck.

sqltest = "SELECT qryAllFieldsForReports.* FROM qryAllFieldsForReports WHERE (((tblLogDetail.Date) Between #" & SQLdate & "# And #" & SQLdate2 & "#))"


In the end I want to be able to do this:

Me.Text18.ControlSource = "=Int((Sum([" & sqltest & "]!TotalCount -([" & sqltest & "]![RandomCount])/(Sum(([" & sqltest & "]!TotalCount))*100))"


Any suggestions on how I can make this work?



[This message has been edited by jerrysmith (edited 10-08-2001).]

 

[Pat Hartman]

If you need data from multiple tables or queries, combine them with a join and use the "new" query as the recordsource for the report. If this won't work because of the structure, you can use domain functions such as DLookup(), DSum(), DMax(), etc, to obtain information in calculated expressions. You cannot just refer to another query in a calculated expression. Recordsets are objects and need to be processed using the correct methods.

[This message has been edited by Pat Hartman (edited 10-09-2001).]