Query by Dates Problem

[Bob Vance]

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!

 

[Bob Vance]

Oops Sorry forgot to show the SQL
SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethod.AmountSummary) 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)));

 

[pietlinden]

Oops Sorry forgot to show the SQL
SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethod.AmountSummary) 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)));

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!

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;

 

[Bob Vance]

Ok Peter my systerm is a bit different it does not have a due date but
calculates from Today()

Oops Sorry forgot to show the SQL
SELECT qOwnerPercentAmountInPaymentMethod.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethod.AmountSummary) 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)));

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!

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

 

[pietlinden]

Ok Peter my systerm is a bit different it does not have a due date but
calculates from Today()<[email protected]> 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));