SQL Query

[Gator]

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

 

[smk23]

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

 

[John Spencer]

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
'====================================================