More Help withthe 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 was shown that I was not adding the <=[Enter DSO Number], while this
worked it would prompt me for Today's 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. If a Dollar amount in the due-date field is 10 before due, I need just
those and I need to make sure it's accurately pulling those that are just -10
days before due- date.

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], now ())) <=[Enter DSO Number]));
 
A

Amy Blankenship

Why do you need to have all those different aging as different columns? It
seems to make more sense to be able to just look down a single column and
see the aging for each customer (it does not appear from what you've said
that your table structure supports multiple agings for the same customer
anyway). If this will work for you, you could do something like this:

IIF (INT(DSO/30)<1, "Current", "["&INT(DSO/30) +1& "-" & (INT(DSO/30) +1) *
30 & "]" AS Aging

Since you're already calculating DSO, maybe if you replace
DateDiff("d",[Due-Date], now () <=[Enter DSO Number])) with DSO <= [Enter
DSO Number] that would work. You might want to try CInt on it as well,
which might strip out the extra characters. I'm not sure enough about how
VBA handles data typing to give you an answer of how to transform "-minus 45
day's" to -45 in one fell swoop. Maybe it's as simple as setting the
parameter type to only accept numbers. However, as written, a negative
number probably would not give you anything in your results set but Current,
because when DSO is a negative number, your first IIF statement will match
and none of the others will (DSO>0).

HTH;

Amy

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 was shown that I was not adding the <=[Enter DSO Number], while this
worked it would prompt me for Today's 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. If a Dollar amount in the due-date field is 10 before due, I need
just
those and I need to make sure it's accurately pulling those that are
just -10
days before due- date.

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], now ())) <=[Enter DSO Number]));
Click to expand...
 
T

tamxwell

With over 60000 records and with all the different queries we need the agings
in thier own prospetive columns. This show as every 30 days the amount rolls
over to the next "Bucket" as we call them. But I believe the change to the
DSO call will work
Thanks !

Amy Blankenship said:
Why do you need to have all those different aging as different columns? It
seems to make more sense to be able to just look down a single column and
see the aging for each customer (it does not appear from what you've said
that your table structure supports multiple agings for the same customer
anyway). If this will work for you, you could do something like this:

IIF (INT(DSO/30)<1, "Current", "["&INT(DSO/30) +1& "-" & (INT(DSO/30) +1) *
30 & "]" AS Aging

Since you're already calculating DSO, maybe if you replace
DateDiff("d",[Due-Date], now () <=[Enter DSO Number])) with DSO <= [Enter
DSO Number] that would work. You might want to try CInt on it as well,
which might strip out the extra characters. I'm not sure enough about how
VBA handles data typing to give you an answer of how to transform "-minus 45
day's" to -45 in one fell swoop. Maybe it's as simple as setting the
parameter type to only accept numbers. However, as written, a negative
number probably would not give you anything in your results set but Current,
because when DSO is a negative number, your first IIF statement will match
and none of the others will (DSO>0).

HTH;

Amy

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 was shown that I was not adding the <=[Enter DSO Number], while this
worked it would prompt me for Today's 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. If a Dollar amount in the due-date field is 10 before due, I need
just
those and I need to make sure it's accurately pulling those that are
just -10
days before due- date.

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], now ())) <=[Enter DSO Number]));
Click to expand...
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

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

Top