Form Sum #error on ADP T-SQL Calculation when applying Filter

[JamesP via AccessMonster.com]

If anyone could be kind enough to help.

I have the following T-SQL statement on Form Record Source:

SELECT CASE WHEN tbl.FinanceApp.FacilityTypeId = 1 THEN 0 ELSE
tblFinanceApp.TotalAmountToBeFinanced END AS SumTotalFinance,
tblFinanceApp.TotalAmountToBeFinanced AS Expr1,
tblFinanceApp.*, tblCustomers.CustomerName, tblSupplierTypes.SupplierType,
tblFinAppSubStatus.SubStatusDescription
FROM tblFinanceApp LEFT OUTER JOIN
tblCustomers ON tblFinanceApp.CustomerID = tblCustomers.
CustomerID LEFT OUTER JOIN
tblSupplierTypes ON tblFinanceApp.Source =
tblSupplierTypes.SupplierTypeId LEFT OUTER JOIN
tblFinAppSubStatus ON tblFinanceApp.FASubStatusId =
tblFinAppSubStatus.FASubStatusId

I do a Form Footer Sum on the SumTotalFinance which works fine, however as
soon as I apply a filter I get a #error.

If anyone has a solution that would solve this please let me know.

Kind Regards,
James.

 

[Sylvain Lafontaine]

In ADP, you cannot locally compute a Sum over an expression ; see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763 .

Maybe you error is related to that.

You should use a parameterized query instead of using a filter (BTW, what
kind of filter, Server Filter or a local Filter?) and compute the Sum
directly on the SQL-Server

 

[Sylvain Lafontaine]

Another possibility would be trying to replace the local Filter with a
Server Filter and vice-versa.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

In ADP, you cannot locally compute a Sum over an expression ; see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763 .

Maybe you error is related to that.

You should use a parameterized query instead of using a filter (BTW, what
kind of filter, Server Filter or a local Filter?) and compute the Sum
directly on the SQL-Server

 

[JamesP via AccessMonster.com]

Thanks for the response. You are absolutely correct. Another way I have
achived this is by utilising the OnApplyFilter event and having (e.g. Note.
Str SQL already contains a WHERE clause

Dim StrSql
Dim ctlCurrentControl As Control
Dim strControlName As String
StrSql = Me.RecordSource
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name
StrSql = StrSql & " And " & strControlName & "=" & ActiveControl.Value
Me.RecordSource = StrSql
Me.Requery

That seemes to work but is a bit of work having (such as text controls
require string parsers) and assuming control names = SQL field names.

James

In ADP, you cannot locally compute a Sum over an expression ; see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763 .

Maybe you error is related to that.

You should use a parameterized query instead of using a filter (BTW, what
kind of filter, Server Filter or a local Filter?) and compute the Sum
directly on the SQL-Server

If anyone could be kind enough to help.

[quoted text clipped - 21 lines]

Kind Regards,
James.

 

[JamesP via AccessMonster.com]

Hi Sylvian,

I believe that is only required if you are using Sum on a calculated
expression. Applying a Filter however requires a total requery with the
Filter selection so that is where the problem is.

Thanks,

James

In ADP, you cannot locally compute a Sum over an expression ; see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763 .

Maybe you error is related to that.

You should use a parameterized query instead of using a filter (BTW, what
kind of filter, Server Filter or a local Filter?) and compute the Sum
directly on the SQL-Server

If anyone could be kind enough to help.

[quoted text clipped - 21 lines]

Kind Regards,
James.

 

[Sylvain Lafontaine]

One good reason to not use a filter.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Hi Sylvian,

I believe that is only required if you are using Sum on a calculated
expression. Applying a Filter however requires a total requery with the
Filter selection so that is where the problem is.

Thanks,

James

In ADP, you cannot locally compute a Sum over an expression ; see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207763 .

Maybe you error is related to that.

You should use a parameterized query instead of using a filter (BTW, what
kind of filter, Server Filter or a local Filter?) and compute the Sum
directly on the SQL-Server

If anyone could be kind enough to help.

[quoted text clipped - 21 lines]

Kind Regards,
James.

 

[JamesP via AccessMonster.com]

Yes, well unfortunately Filter by selection is a very flexible and handy tool
here. If it wasn't for the Sums on Total Amount Financed at the bottom of the
forms there would not be a problem.

Cheers,
James.

One good reason to not use a filter.

Hi Sylvian,

[quoted text clipped - 20 lines]