Aging By Date

[Ted]

Working with payable information. Ultimately, I want a
report that shows and totals invoices that are grouped in
the folowing buckets:

less than 30 days past due
31-60 days past due
61-90 days past due
91 or more days past due.

Ex: If the report were run today (12/16/03) then an
invoice due 12/10/03 would be included in the less than 30
days total and an invoice due 11/08/03 would be included
in the 31-60 days total.

So I want to compare the invoice due date to a user
defined/input date and then group and total them in the
date range buckets.

Any ideas, suggestions or guidance?

Thanks....

 

[John Nurick]

Hi Ted,

You can sort and group on a calculated field like this:

Age: Switch(CDate([Enter date])-[TheDate]<31,1,CDate([Enter
date])-[TheDate]<61,2,CDate([Enter date])-[TheDate]>60,3)

 

[Ted]

Hey John,

I appreciate the time you took to respond.

However, I am getting an #error in the field when I employ
this expression. So maybe I'm not using it correctly or I
don't understand what this expression does. I have a field
in my table called Due Date which is the value I need to
compare the user input date against. So I took your
example and substituted Due Date for [The Date].

Specifically, my questions are:

1. Exactly what is this expression supposed to do?
2. What do the Switch and CDate commands accomplish?

Thanks again for your patience and time...

-----Original Message-----
Hi Ted,

You can sort and group on a calculated field like this:

Age: Switch(CDate([Enter date])-[TheDate]<31,1,CDate ([Enter
date])-[TheDate]<61,2,CDate([Enter date])-[TheDate]>60,3)

Working with payable information. Ultimately, I want a
report that shows and totals invoices that are grouped in
the folowing buckets:

Ex: If the report were run today (12/16/03) then an
invoice due 12/10/03 would be included in the less than 30
days total and an invoice due 11/08/03 would be included
in the 31-60 days total.

So I want to compare the invoice due date to a user
defined/input date and then group and total them in the
date range buckets.

Any ideas, suggestions or guidance?

Thanks....

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Ted]

John,

After playing with it for awhile, I got it to work. Thanks
for the help. I would still be interested in the
explanation of CDate and Switch.

Thanks again for your help....

-----Original Message-----
Hey John,

I appreciate the time you took to respond.

However, I am getting an #error in the field when I employ
this expression. So maybe I'm not using it correctly or I
don't understand what this expression does. I have a field
in my table called Due Date which is the value I need to
compare the user input date against. So I took your
example and substituted Due Date for [The Date].

Specifically, my questions are:

1. Exactly what is this expression supposed to do?
2. What do the Switch and CDate commands accomplish?

Thanks again for your patience and time...

-----Original Message-----
Hi Ted,

You can sort and group on a calculated field like this:

Age: Switch(CDate([Enter date])-[TheDate]<31,1,CDate ([Enter
date])-[TheDate]<61,2,CDate([Enter date])-[TheDate]>60,3)

Working with payable information. Ultimately, I want a
report that shows and totals invoices that are grouped in
the folowing buckets:
less than 30 days past due
31-60 days past due
61-90 days past due
91 or more days past due.

Ex: If the report were run today (12/16/03) then an
invoice due 12/10/03 would be included in the less than 30
days total and an invoice due 11/08/03 would be included
in the 31-60 days total.

So I want to compare the invoice due date to a user
defined/input date and then group and total them in the
date range buckets.

Any ideas, suggestions or guidance?

Thanks....

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.

 

[John Nurick]

Hi Ted,

Glad it's working.

CDate() converts the string you entered (e.g. "12/12/03") into an Access
date/time value so it can be compared with the value in the Due Date
field. (Access stores dates and times as serial numbers: e.g. 12 Dec 03
is 37967, and noon on 13 Dec is 37968.5)

Switch() takes pairs of arguments. It starts by evaluating the first
argument in the first pair. If the result is non-zero (True) it
evaluates the second argument and returns the result; otherwise it skips
to the first argument of the next pair and evaluates that. See Help for
details.

John,

After playing with it for awhile, I got it to work. Thanks
for the help. I would still be interested in the
explanation of CDate and Switch.

Thanks again for your help....

-----Original Message-----
Hey John,

I appreciate the time you took to respond.

However, I am getting an #error in the field when I employ
this expression. So maybe I'm not using it correctly or I
don't understand what this expression does. I have a field
in my table called Due Date which is the value I need to
compare the user input date against. So I took your
example and substituted Due Date for [The Date].

Specifically, my questions are:

1. Exactly what is this expression supposed to do?
2. What do the Switch and CDate commands accomplish?

Thanks again for your patience and time...

-----Original Message-----
Hi Ted,

You can sort and group on a calculated field like this:

Age: Switch(CDate([Enter date])-[TheDate]<31,1,CDate ([Enter
date])-[TheDate]<61,2,CDate([Enter date])-[TheDate]>60,3)

On Tue, 16 Dec 2003 14:08:23 -0800, "Ted"

Working with payable information. Ultimately, I want a
report that shows and totals invoices that are grouped in
the folowing buckets:
less than 30 days past due
31-60 days past due
61-90 days past due
91 or more days past due.

Ex: If the report were run today (12/16/03) then an
invoice due 12/10/03 would be included in the less than 30
days total and an invoice due 11/08/03 would be included
in the 31-60 days total.

So I want to compare the invoice due date to a user
defined/input date and then group and total them in the
date range buckets.

Any ideas, suggestions or guidance?

Thanks....

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

.