G
Guest
This query is for Disputed items, and there are 3 values that are used D, P,
1. My query is written as IS NOT NULL. This works fine, but now I need to
exclude D. I’ve tried it a number of ways but……… Here the query.
Thanks
SELECT [tblARDetail].[co-number] AS Branch, [tblARDetail].[divn-number] AS
SubBranch, [tblARDetail].[cust-number] AS CustomerNumber,
[tblARDetail].[cust-name] AS CustomerName, [tblARDetail]![trans-code] &
[tblARDetail]![item-type] AS TranType, [tblARDetail].[ref-number] AS
ReferenceNumber, [tblARDetail].[as-of-date], [tblARDetail].[due-date],
[tblARDetail].[item-amount], Now() AS TODAY, DateDiff("d",[Due-Date],[Today])
AS DSO,
IIf([item-amount]<0,[item-amount],IIf([due-date]>[Today],[item-amount],0)) AS
[Current], IIf([item-amount]<0,0,IIf(([DSO])<31 And
([DSO])>0,[item-amount],0)) AS [1-30], IIf([item-amount]<0,0,IIf(([DSO])<61
And ([DSO])>31,[item-amount],0)) AS [31-60],
IIf([item-amount]<0,0,IIf(([DSO])<91 And ([DSO])>=61,[item-amount],0)) AS
[61-90], IIf([item-amount]<0,0,IIf(([DSO])<181 And
([DSO])>=91,[item-amount],0)) AS [91-180],
IIf([item-amount]<0,0,IIf(([DSO])<360 And ([DSO])>=181,[item-amount],0)) AS
[181-360], IIf([item-amount]<0,0,IIf(([DSO])>=360,[item-amount],0)) AS
[360+], [tblARDetail].[status-code] AS Disputed, [tblCustusage].[CA],
[tblCustusage].[CM]
FROM tblARDetail INNER JOIN tblCustusage ON
([tblARDetail].[cust-number]=[tblCustusage].[Acct]) AND
([tblARDetail].[co-number]=[tblCustusage].[Company])
WHERE ((([tblARDetail].[status-code]) Is Not Null));
1. My query is written as IS NOT NULL. This works fine, but now I need to
exclude D. I’ve tried it a number of ways but……… Here the query.
Thanks
SELECT [tblARDetail].[co-number] AS Branch, [tblARDetail].[divn-number] AS
SubBranch, [tblARDetail].[cust-number] AS CustomerNumber,
[tblARDetail].[cust-name] AS CustomerName, [tblARDetail]![trans-code] &
[tblARDetail]![item-type] AS TranType, [tblARDetail].[ref-number] AS
ReferenceNumber, [tblARDetail].[as-of-date], [tblARDetail].[due-date],
[tblARDetail].[item-amount], Now() AS TODAY, DateDiff("d",[Due-Date],[Today])
AS DSO,
IIf([item-amount]<0,[item-amount],IIf([due-date]>[Today],[item-amount],0)) AS
[Current], IIf([item-amount]<0,0,IIf(([DSO])<31 And
([DSO])>0,[item-amount],0)) AS [1-30], IIf([item-amount]<0,0,IIf(([DSO])<61
And ([DSO])>31,[item-amount],0)) AS [31-60],
IIf([item-amount]<0,0,IIf(([DSO])<91 And ([DSO])>=61,[item-amount],0)) AS
[61-90], IIf([item-amount]<0,0,IIf(([DSO])<181 And
([DSO])>=91,[item-amount],0)) AS [91-180],
IIf([item-amount]<0,0,IIf(([DSO])<360 And ([DSO])>=181,[item-amount],0)) AS
[181-360], IIf([item-amount]<0,0,IIf(([DSO])>=360,[item-amount],0)) AS
[360+], [tblARDetail].[status-code] AS Disputed, [tblCustusage].[CA],
[tblCustusage].[CM]
FROM tblARDetail INNER JOIN tblCustusage ON
([tblARDetail].[cust-number]=[tblCustusage].[Acct]) AND
([tblARDetail].[co-number]=[tblCustusage].[Company])
WHERE ((([tblARDetail].[status-code]) Is Not Null));