SQL Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gator - 18 Apr 2008 22:04 GMT

I have a form that displays in tabular form a table. The form footer
contains a textbox that sums the 'amount' field. I want to add another
textbox that displays the sum of the 'amount' of a query of records where
another field is queried.

For example. =SUM(Amount) WHERE City LIKE 'B%'

I can't get this to work....is there something i'm doing wrong?

Many Thx

Signature

Gator

Reply to this Message

smk23 - 19 Apr 2008 17:11 GMT

THe problem may be use of SQL syntax within MSAccess. Access uses double
quotes " where SQL uses the single quote ' and the wildcard operator in
Access is * rather than %

HTH
Sam

> I have a form that displays in tabular form a table. The form footer
> contains a textbox that sums the 'amount' field. I want to add another
[quoted text clipped - 6 lines]
>
> Many Thx

Reply to this Message

John Spencer - 19 Apr 2008 20:37 GMT

If Amount is always positive the you could try this.

Abs(SUM(City Like 'B%' * Amount))

Otherwise

Sum(IIF(City like 'B%',Amount,Null))

If the like fails to work for you try changing the wild card character
to an asterisk "*".

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> I have a form that displays in tabular form a table. The form footer
> contains a textbox that sums the 'amount' field. I want to add another
[quoted text clipped - 6 lines]
>
> Many Thx

Reply to this Message