Grouping dates

[Guest]

I am entering information in a form and todays date is captured as loan
entered date, I have a followup date which one will enter as a future date.
In my report, I am trying to pull information based on followup date that
is late < 2 days, between 3 to 5 days and > than 5 days.... How do I tell the
system to group that information that is late as stated above?

Please help!

 

[David Seeto via AccessMonster.com]

Well, the first thing you need to do is create a calculated column in your
query for how late the loan is
DaysLate: [FollowUpDate]-[LoanEnteredDate]

Then you can apply criteria to this field as you describe (although if
something is between 2 and 3 days late, your criteria won't identify them).

If you want ot show these side by side in the same record, you will need to
create three queries showing the individual categories and then join all
those tables together.

 

[tw]

Make you report based on a query something like this

SELECT tblTest.loan, tblTest.future, IIf(Now()-[future]<=2,"Group
1",IIf(Now()-[Future]>3 And Now()-[future]<5,"Group
2",IIf(Now()-[future]>=5,"Group 3","Not in Scope"))) AS Grouping
FROM tblTest

This query has a field called grouping that has the following data...
"Group 1" dates that are 2 days or less
"Group 2" dates that are between 3 and 5 days late
"Group 3" dates that are 5 or more days late

this is if your comparison is between todays date and the future date
entered (assuming that now we are late because it has passed the future date
entered)

if you comparison is between your loan entered date and your future date
change the query's grouping fields calculation to [future] - [entered]
instead of now() - [future]

 

[tw]

If this is for some kind of aging report for AR then you can change the
query to have a group1 bucket, a group 2 bucket and a group 3 bucket. You
can use the query below the same way, but it might be simpler depending on
your report split the field into three boolean fields... something like this

select tblTest loanentered, futuredate, iif(now()-[futuredate] <= 2,
true,false) as Group1,iif(now() - [futuredate] >3 and now() - [futuredate] <
5, true,false) as Group2, iif(now()-[futuredate] >=5,true,false) as Group3
from tblTest

Make you report based on a query something like this

SELECT tblTest.loan, tblTest.future, IIf(Now()-[future]<=2,"Group
1",IIf(Now()-[Future]>3 And Now()-[future]<5,"Group
2",IIf(Now()-[future]>=5,"Group 3","Not in Scope"))) AS Grouping
FROM tblTest

This query has a field called grouping that has the following data...
"Group 1" dates that are 2 days or less
"Group 2" dates that are between 3 and 5 days late
"Group 3" dates that are 5 or more days late

this is if your comparison is between todays date and the future date
entered (assuming that now we are late because it has passed the future
date entered)

if you comparison is between your loan entered date and your future date
change the query's grouping fields calculation to [future] - [entered]
instead of now() - [future]

I am entering information in a form and todays date is captured as loan
entered date, I have a followup date which one will enter as a future
date.
In my report, I am trying to pull information based on followup date
that
is late < 2 days, between 3 to 5 days and > than 5 days.... How do I tell
the
system to group that information that is late as stated above?

Please help!

 

[Guest]

Thanks you so much, that worked so well and I learnt something too!
OJ!

Make you report based on a query something like this

SELECT tblTest.loan, tblTest.future, IIf(Now()-[future]<=2,"Group
1",IIf(Now()-[Future]>3 And Now()-[future]<5,"Group
2",IIf(Now()-[future]>=5,"Group 3","Not in Scope"))) AS Grouping
FROM tblTest

This query has a field called grouping that has the following data...
"Group 1" dates that are 2 days or less
"Group 2" dates that are between 3 and 5 days late
"Group 3" dates that are 5 or more days late

this is if your comparison is between todays date and the future date
entered (assuming that now we are late because it has passed the future date
entered)

if you comparison is between your loan entered date and your future date
change the query's grouping fields calculation to [future] - [entered]
instead of now() - [future]

I am entering information in a form and todays date is captured as loan
entered date, I have a followup date which one will enter as a future
date.
In my report, I am trying to pull information based on followup date that
is late < 2 days, between 3 to 5 days and > than 5 days.... How do I tell
the
system to group that information that is late as stated above?

Please help!

 

[tw]

glad to help.

Thanks you so much, that worked so well and I learnt something too!
OJ!

Make you report based on a query something like this

SELECT tblTest.loan, tblTest.future, IIf(Now()-[future]<=2,"Group
1",IIf(Now()-[Future]>3 And Now()-[future]<5,"Group
2",IIf(Now()-[future]>=5,"Group 3","Not in Scope"))) AS Grouping
FROM tblTest

This query has a field called grouping that has the following data...
"Group 1" dates that are 2 days or less
"Group 2" dates that are between 3 and 5 days late
"Group 3" dates that are 5 or more days late

this is if your comparison is between todays date and the future date
entered (assuming that now we are late because it has passed the future
date
entered)

if you comparison is between your loan entered date and your future date
change the query's grouping fields calculation to [future] - [entered]
instead of now() - [future]

I am entering information in a form and todays date is captured as loan
entered date, I have a followup date which one will enter as a future
date.
In my report, I am trying to pull information based on followup date
that
is late < 2 days, between 3 to 5 days and > than 5 days.... How do I
tell
the
system to group that information that is late as stated above?

Please help!