HELP WITH "WHERE" clause

T

tamxwell

This is a complex query that shows debt agings for all our customers from
CURRENT, 1-30, 31-60, 61-90, 91-181, 181-360, 360+ days. I have 2 tables
joined, ARDetail and Custusage these have specific fields from the text
files. I used IIF to do the calculations for each of these fields listed
above. DSO stands for Day’s Sales Overdue. So my DSO field is
[due-date],[today] which is due date minus today’s date, this tells the
Credit Managers how many day’s their particular customer is overdue. In the
WHERE clause I have it prompting for the CM’s 2 digit number. THEN I need
them to be able to enter, say –minus 45 day’s or 15 day’s etc. (This would
stand for –45 overdue. )

This is were I need help. When they are prompted for their CM number they
can enter it, but when they are prompted for the DSO number it just pulls
that specific number and not “What is 45 or 15 or 20 day’s overdueâ€. The
DateDiff(“dâ€,[due-date],[today] is used to round off the day to a whole
number, and I am not sure if this is the problem, but I need to pull the info
from this field. I will list my SELECT query below. I am sure that I will
have to explain this more, I just figured I’d start here. 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+],
tblCustusage.CA, tblCustusage.CM

FROM tblARDetail INNER JOIN tblCustusage ON (tblARDetail.[cust-number] =
tblCustusage.Acct) AND (tblARDetail.[co-number] = tblCustusage.Company)



WHERE((( tblCustusage.CM)=[Which CM do you want data for?]) and ((
DateDiff("d",[Due-Date],[Today]))=[Enter DSO Number]));
 
M

MGFoster

tamxwell said:
This is a complex query that shows debt agings for all our customers from
CURRENT, 1-30, 31-60, 61-90, 91-181, 181-360, 360+ days. I have 2 tables
joined, ARDetail and Custusage these have specific fields from the text
files. I used IIF to do the calculations for each of these fields listed
above. DSO stands for Day’s Sales Overdue. So my DSO field is
[due-date],[today] which is due date minus today’s date, this tells the
Credit Managers how many day’s their particular customer is overdue. In the
WHERE clause I have it prompting for the CM’s 2 digit number. THEN I need
them to be able to enter, say –minus 45 day’s or 15 day’s etc. (This would
stand for –45 overdue. )

This is were I need help. When they are prompted for their CM number they
can enter it, but when they are prompted for the DSO number it just pulls
that specific number and not “What is 45 or 15 or 20 day’s overdueâ€. The
DateDiff(“dâ€,[due-date],[today] is used to round off the day to a whole
number, and I am not sure if this is the problem, but I need to pull the info
from this field. I will list my SELECT query below. I am sure that I will
have to explain this more, I just figured I’d start here. 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+],
tblCustusage.CA, tblCustusage.CM

FROM tblARDetail INNER JOIN tblCustusage ON (tblARDetail.[cust-number] =
tblCustusage.Acct) AND (tblARDetail.[co-number] = tblCustusage.Company)



WHERE((( tblCustusage.CM)=[Which CM do you want data for?]) and ((
DateDiff("d",[Due-Date],[Today]))=[Enter DSO Number]));
Click to expand...


Looks like the answer is <= instead of = :

DateDiff("d",[Due-Date],[Today])) <= [Enter DSO Number]));
 
T

tamxwell

Question, I added the< to the WHERE clause and it work, but it would prompt
me for the date, so I changed DateDiff("d",[Due-Date],[Today])) <= [Enter
DSO Number]));...from [Today] to NOW() <= [Enter DSO Number])); and again it
work perfectly. Today I come in and now when I enter -10 (for say everything
10 before due) it gives me 0 or -1. I need to menition that yesterday a
Vice-Pres. came in to look over some operations and my Boss and her changed
some items in another query I built for them, they say they did not touch,
they were just looking. (In to which I put a stop to......) but now I am
looking at everything and just getting madder by the minute. Anyway I will
paste it below again, and if a fresh pair of eyes could look at it for me.


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], tblCustusage.CA, tblCustusage.CM

FROM tblARDetail INNER JOIN tblCustusage ON (tblARDetail.[cust-number] =
tblCustusage.Acct) AND (tblARDetail.[co-number] = tblCustusage.Company)

WHERE (((tblCustusage.CM)=[Which CM do you want data for?]) AND
((DateDiff("d",[Due-Date], now()) <=[Enter DSO Number])));






MGFoster said:
tamxwell said:
This is a complex query that shows debt agings for all our customers from
CURRENT, 1-30, 31-60, 61-90, 91-181, 181-360, 360+ days. I have 2 tables
joined, ARDetail and Custusage these have specific fields from the text
files. I used IIF to do the calculations for each of these fields listed
above. DSO stands for Day’s Sales Overdue. So my DSO field is
[due-date],[today] which is due date minus today’s date, this tells the
Credit Managers how many day’s their particular customer is overdue. In the
WHERE clause I have it prompting for the CM’s 2 digit number. THEN I need
them to be able to enter, say –minus 45 day’s or 15 day’s etc. (This would
stand for –45 overdue. )

This is were I need help. When they are prompted for their CM number they
can enter it, but when they are prompted for the DSO number it just pulls
that specific number and not “What is 45 or 15 or 20 day’s overdueâ€. The
DateDiff(“dâ€,[due-date],[today] is used to round off the day to a whole
number, and I am not sure if this is the problem, but I need to pull the info
from this field. I will list my SELECT query below. I am sure that I will
have to explain this more, I just figured I’d start here. 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+],
tblCustusage.CA, tblCustusage.CM

FROM tblARDetail INNER JOIN tblCustusage ON (tblARDetail.[cust-number] =
tblCustusage.Acct) AND (tblARDetail.[co-number] = tblCustusage.Company)



WHERE((( tblCustusage.CM)=[Which CM do you want data for?]) and ((
DateDiff("d",[Due-Date],[Today]))=[Enter DSO Number]));
Click to expand...


Looks like the answer is <= instead of = :

DateDiff("d",[Due-Date],[Today])) <= [Enter DSO Number]));
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

More Help withthe WHERE Clause 2
Using NULL 1
need only the records where..... 2
calculating sums 21
USING THE "IN CLAUSE" 4
Calculating sums Question 4
UNION ALL- CALCULATING SUMS 1
UNION ALL CALCULATING SUMS 0

Top