I'm trying to generate an Age Analysis Report where the foll SQL
statement will executed be in a loop for each customer.

Can someone please correct my SQL code and/or provide any ideas ?


strSQL2 = "select Sum(nz(DPATDAT.[DEBIT])) AS SumOf0to30 " &_
" WHERE Now()-[DPATDAT.Inv_Date] >=0 and Now()-
[DPATDAT.Inv_Date] <=30, " & _
" Sum(nz(DPATDAT.[DEBIT])) AS SumOf31to60 " &_
" WHERE Now()-[DPATDAT.Inv_Date] >=31 and Now()-
[DPATDAT.Inv_Date] <=60, " & _
" Sum(nz(DPATDAT.[DEBIT])) AS SumOf61to90 " &_
" WHERE Now()-[DPATDAT.Inv_Date] >=61 and Now()-
[DPATDAT.Inv_Date] <=90, " & _
" Sum(nz(DPATDAT.[DEBIT])) AS SumOfgt90
WHERE Now()-[DPATDAT.Inv_Date]


Thx & best Rgds,\Prakash

On Apr 15, 2:22*pm, prakashwadhw... (AT) gmail (DOT) com wrote:


Ok ... I guess you'll may have understood from my above pseudo-SQL
what I'm trying to achieve but I've tried something on a smaller level
now. I'm just attempting a single SQL construct which is still
throwing up an error. Could someone please help ? Here's my new
construct ...

strSQL1 = "SELECT Sum(nz(DPATDAT.[DEBIT])) AS SumOf0to30 " &_
"FROM DPATDAT " & _
"WHERE Now()-[DPATDAT.Inv_Date] >= 0 " &_
"AND Now()-[DPATDAT.Inv_Date] <= 30 "

I guess if my FIRST SQL statement is not possible then perhaps I could
make 6 such statements for each AGE Group.

Looking forward to some help.

Best Rgds,
Prakash.

prakashwadhwani (AT) gmail (DOT) com wrote:
contain hh:mm:ss?. Ex:
? now() - date
0.328576388885267
? now() - date-1
-0.670405092590954
? date() - date-1
-1
Subracting where both are date() instead of a now() will return a whole
number.

Filtering like you have done might retrieve more accurate results if you
used Dsum(). Ex:

strSQL2 = "select DSum("DEBIT","DPATDAT","InvDate - date() >= 0 and
InvDate - date() <= 30) AS SumOf0to30

Instead of using Dsum, maybe Esum. http://allenbrowne.com/ser-66.html

"AND Now()-[DPATDAT.Inv_Date] <= 30 "
<<

You need to use the DateDiff function to perform the date subtraction
operations. Sql has no idea if you are subtracting days, months, years,
minutes.

Here is a sample from the help files for subtracting days:

Sub dateSubtract()
Dim d1 As Date
d1 = "4/4/09"
Debug.Print DateDiff("d", d1, Now)
End Sub

this would print 11 --- 11 days difference between 4/4/09 and Now.
Here is how to use it in a sql statement:

Set RS = CurrentDb.OpenRecordset("select sum(fld1) from tbl1 where
datediff('d',datefld,now()) > 11")
Debug.Print RS(0)

Note: when using DateDiff in a sql statement the time interval
parameter (in my case days d) is delimited with single quotes ' '. When
not in a sql statement, you delimit the time interval parameter with
doubl quotes " ".

Rich

*** Sent via Developersdex http://www.developersdex.com ***

prakashwadhwani (AT) gmail (DOT) com wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the Partition() function to get the number of days:

SELECT Partition(Date()-Inv_Date, 31, 90, 30) As DayRange,
SUM(Debit) As TotalDebit
FROM DPatDat
WHERE Inv_Date < Date()
GROUP BY Partition(Date()-Inv_Date, 31, 90, 30)

Any debit less than, or equal to, 30 days will have a partition of
" :30". Any debit over 90 days will have a partition of "91: ".
Everything in-between will have a partition of 31:60 or 61:90.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeYIW4echKqOuFEgEQLaeACg5Zph3fh5L4rTL8ARl34Gdi ocI9AAnjpt
qG8ZMiPv6R+Nzre2c40dUFDV
=pYkw
-----END PGP SIGNATURE-----

On Apr 15, 7:04*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Hi Salad ... thx for replying.

I'm sorry - you are right. DPATDAT.Inv_Date IS a regular date field
and so you've pointed out my mistake correctly.

Could you please tell me why the Dsum would be better to use than the
SUM in the select statement ?

In the meanwhile I'll try correcting my syntax & get back if there's
any further problems.

Best Rgds,
Prakash.

prakashwadhwani (AT) gmail (DOT) com wrote:

Because you can associate a filter to the Suming process. Maybe you are
using something else besides an MDB backend and can use the syntax like
above.

On Apr 15, 10:02*pm, Salad <o... (AT) vinegar (DOT) com> wrote:


Hi Salad,

I have tried it but somehow there seems to be some problem in the
filter. No syntax error ... just that no values are being picked up
from the table. Here is my statement:

SumOf0to30 = Nz(DSum("[DEBIT]", "DPATDAT", "[code] = " & Me.txt_Code &
" AND [Inv_Date] - date() >= 0 AND [Inv_Date] - date() <= 30 "))

I have singled out the [code] = " & Me.txt_Code part and that is
perfect. It's the date part which is creating a problem.

Now, I must say my date format everywhere is dd/mm/yy. Could that be
creating a problem ?

I'd greatly appreciate any help.

Thx again to everyone,
Prakash.

prakashwadhwani (AT) gmail (DOT) com wrote:
If you use NZ, you should supply two values. Ex:
? IIF(TrueValue,1,0)
I don't see your false value in the example above. Ex:
? NZ(Dsum("FieldName","TableName","ID = 1"),0)

On Apr 16, 10:02*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

I was always under the impression that the "ValueIfNull" i.e. 2nd
argument of the NZ function was optional and so far it has worked for
me. However, if you feel I should change it and compulsorily include
the 2nd argument, then I'll go ahead and do so. I'd really appreciate
an explanation as to why though.

Also ... I've finally got the code to work. I'm posting it below. I've
used the datediff function as suggested by Rich and so would like to
thank him for his input.

SumOfgt120 = Nz(DSum("[DEBIT]", "DPATDAT", "[code] = " & Me.txt_Code &
" AND datediff('d', [Inv_Date], Now()) >= 121 "), 0)
the code works fine without the optional 0 of the NZ function. I've
just added that for extra caution as suggested by you.

I'd also like to thank MG Foster for his suggestion. I had initially
tried the partition function but couldn't get it to work the way I'd
wanted. I'll probably start another thread for that once I've sorted
out this problem and sincerely hope you'll be around to help me
through that.

Best Rgds,
Prakash.