Simple problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

solar - 16 Mar 2007 14:24 GMT

HI...

Can anybody solve this problem..?

One small problem...

In the following query, i should get only Non-Zero balance in the
Balance_Amount expressions.ie,. the balance amount should not have any '0'
balance. I tried many time, but i couldn't get....can anybody point out
me....where it is ...?

I have given in SQL VIEW...

SELECT Requistion_Table.Req_Region_Name, Requistion_Table.Req_Vendor_Name,
Requistion_Table.Req_Payable_Amt, Sum(Payment_Table.Paid_Amt) AS
SumOfPaid_Amt, ([Req_Payable_Amt]-[SumOfPaid_Amt]) AS [Balance_Amount],
Requistion_Table.Req_Date, Payment_Table.Pay_InvNo
FROM Company_Table, Payment_Table INNER JOIN Requistion_Table ON
Payment_Table.Pay_InvNo = Requistion_Table.Req_Inv_No GROUP BY
Requistion_Table.Req_Region_Name, Requistion_Table.Req_Vendor_Name,
Requistion_Table.Req_Payable_Amt, Requistion_Table.Req_Date,
Payment_Table.Pay_InvNo
HAVING (((Requistion_Table.Req_Date) Between [Enter the FROM Date
(DD/MM/YY)] And [Enter the TO Date (DD/MM/YY)]));

Thnx..in advance...

Reply to this Message

Jason Lepack - 16 Mar 2007 14:55 GMT

You can't use a calculated field in a where clause, therefore you
won't be able to use SumOfPaid_Amt or Balance_Amount.

The easiest quick fix, is to create a new query which uses this query
as it's data source and filter the zeroes.

SELECT *
FROM solar_query
WHERE Balance_Amount <> 0

Cheers,
Jason Lepack

> HI...
>
[quoted text clipped - 22 lines]
>
> Thnx..in advance...

Reply to this Message

solar - 17 Mar 2007 05:53 GMT

Dear Jason Lepack,

Its simply great....Its working fine.

Thnx..a...lot...

Thnx..

Solar.

> You can't use a calculated field in a where clause, therefore you
> won't be able to use SumOfPaid_Amt or Balance_Amount.
[quoted text clipped - 35 lines]
> >
> > Thnx..in advance...

Reply to this Message