NEED HELP WITH SQL QUERY. CAN'T GET IT. 1

[faxpay]

I am using an Access 2000 database and am writing the following query:

"SELECT SUM(MID(BOX12_A,2))+ SUM(MID(BOX12_B,2)) + SUM(MID(BOX12_C,2))" _
& " AS RESULT" _
& " FROM EMPLYEE_INFO WHERE BOX12_A LIKE 'D%' OR BOX12_B LIKE 'D%'" _
& " OR BOX12_C LIKE 'D%'"

The problem seems to be in the WHERE portion. I have tried using "OR" and "AND" but to no avail. The sum's in themselves work ok but can't get that "WHERE" portion down.

I want one sum for items in all 3 fields and include only those items that begin with a "D". The items or amounts in the fields are preceded by a letter like D 657.88, A 65.99, W 45.99, etc.

Thank you for looking at this.

faxpay, TOM

 

[Golom]

Maybe you need something like

SELECT 
(
SUM(IIF(Left(BOX12_A,1)="D",MID(BOX12_A,2),0))+ 
SUM(IIF(Left(BOX12_B,1)="D",MID(BOX12_B,2),0))+ 
SUM(IIF(Left(BOX12_C,1)="D",MID(BOX12_C,2),0))) AS RESULT

FROM EMPLYEE_INFO

Your current WHERE clause is going to SUM all three fields as long as any one of them starts with "D".

 

[faxpay]

Golom,

That query you gave me works perfect. I thank you immensely(big!).

Still not sure why my query did not work.

Thanks again. faxpay, Tom

 

[r937]

it didn't work because the wildcard character for access is * not %

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)