Simple problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
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
 
Dear Jason Lepack,

Its simply great....Its working fine.

Thnx..a...lot...

Thnx..

Solar.

Jason Lepack said:
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...

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...
Click to expand...
Click to expand...
 
Back
Top