My query has 3 fields
1-OwnerID, Group by
2-Dues: AmountSummary, Sum of what he totaly owes
3-MD:
IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
Not working properly trying to get 1,2,3, Overdue Totals, What do you think
looks wrong!
--
Thanks in advance for any help with this......Bob
WindowsXP..MS Access 2007

Oops Sorry forgot to show the SQL
SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethod.AmountSumma ry) AS Dues,
IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
AS MD
FROM qOwnerPercentAmountInPaymentMethod
GROUP BY qOwnerPercentAmountInPaymentMethod.OwnerID,
IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)));

"Bob Vance" <rjvance@ihug.co.nz> wrote in message
news:ujFznpD$IHA.3648@TK2MSFTNGP02.phx.gbl...
> My query has 3 fields
> 1-OwnerID, Group by
> 2-Dues: AmountSummary, Sum of what he totaly owes
> 3-MD:
> IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
> Not working properly trying to get 1,2,3, Overdue Totals, What do you
> think looks wrong!
> --
> Thanks in advance for any help with this......Bob
> WindowsXP..MS Access 2007
>

On Aug 12, 12:21*am, "Bob Vance" <rjva...@ihug.co.nz> wrote:
> Oops Sorry forgot to show the SQL
> SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
> Sum(qOwnerPercentAmountInPaymentMethod.AmountSumma ry) AS Dues,
> IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
> AS MD
> FROM qOwnerPercentAmountInPaymentMethod
> GROUP BY qOwnerPercentAmountInPaymentMethod.OwnerID,
> IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)));
>
> "Bob Vance" <rjva...@ihug.co.nz> wrote in message
>
> news:ujFznpD$IHA.3648@TK2MSFTNGP02.phx.gbl...
>
> > My query has 3 fields
> > 1-OwnerID, Group by
> > 2-Dues: AmountSummary, Sum of what he totaly owes
> > 3-MD:
> > IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
> > Not working properly trying to get 1,2,3, Overdue Totals, What do you
> > think looks wrong!
> > --
> > Thanks in advance for any help with this......Bob
> > WindowsXP..MS Access 2007

SELECT tDues.MemberID, tDues.DueDate, tDues.AmountDue, tDues.DatePaid,
IIf(IsNull([DatePaid]),DateDiff("d",[tDues]![DueDate],Date()),0) AS
DaysOverdue, IIf(IsNull([DatePaid]),DateDiff("m",[tDues]!
[DueDate],Date()),0) AS MonthsOverdue
FROM tDues;

Ok Peter my systerm is a bit different it does not have a due date but
calculates from Today()
<pietlinden@hotmail.com> wrote in message
news:7c8d3bc6-d4fe-4b29-944d-e8fc96f2f875@x35g2000hsb.googlegroups.com...
On Aug 12, 12:21 am, "Bob Vance" <rjva...@ihug.co.nz> wrote:
> Oops Sorry forgot to show the SQL
> SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
> Sum(qOwnerPercentAmountInPaymentMethod.AmountSumma ry) AS Dues,
> IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
> AS MD
> FROM qOwnerPercentAmountInPaymentMethod
> GROUP BY qOwnerPercentAmountInPaymentMethod.OwnerID,
> IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)));
>
> "Bob Vance" <rjva...@ihug.co.nz> wrote in message
>
> news:ujFznpD$IHA.3648@TK2MSFTNGP02.phx.gbl...
>
> > My query has 3 fields
> > 1-OwnerID, Group by
> > 2-Dues: AmountSummary, Sum of what he totaly owes
> > 3-MD:
> > IIf((Date()-[ondate])/30>=3,3,IIf((Date()-[ondate])/30>=2,2,IIf((Date()-[ondate])/30>=1,1,0)))
> > Not working properly trying to get 1,2,3, Overdue Totals, What do you
> > think looks wrong!
> > --
> > Thanks in advance for any help with this......Bob
> > WindowsXP..MS Access 2007

SELECT tDues.MemberID, tDues.DueDate, tDues.AmountDue, tDues.DatePaid,
IIf(IsNull([DatePaid]),DateDiff("d",[tDues]![DueDate],Date()),0) AS
DaysOverdue, IIf(IsNull([DatePaid]),DateDiff("m",[tDues]!
[DueDate],Date()),0) AS MonthsOverdue
FROM tDues;

Ok Peter my systerm is a bit different it does not have a due date but
calculates from Today()
Thanks anyway for the help,,,,,,,Bob

On Aug 12, 2:05*am, "Bob Vance" <rjva...@ihug.co.nz> wrote:
> Ok Peter my systerm is a bit different it does not have a due date but
> calculates from Today()<pietlin...@hotmail.com> wrote in message

Huh? Mine calculates from today too... that's that Date() is.

So you're trying to aging? Sounds like you need 3 DSUMS with
different criteria.
DatePaid is False/Null whatever,
then the ranges for each are different.

You mean something like this:
TRANSFORM Sum(Query2.AmountDue) AS SumOfAmountDue
SELECT Query2.MemberID, Sum(Query2.AmountDue) AS [Total Of AmountDue]
FROM Query2
GROUP BY Query2.MemberID
PIVOT Query2.MonthsOverdue;

based on this (Query2 SQL):

SELECT tDues.MemberID, tDues.DueDate, tDues.AmountDue, tDues.DatePaid,
IIf(IsNull([DatePaid]),DateDiff("d",tDues!DueDate,Date()),0) AS
DaysOverdue, IIf(IsNull([DatePaid]),DateDiff("d",[tDues]!
[DueDate],Date()),0)\30 AS MonthsOverdue
FROM tDues
WHERE (((IIf(IsNull([DatePaid]),DateDiff("d",[tDues]![DueDate],Date()),
0)\30)>0));