IIF Statement to calculate aging buckets

[Guest]

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input the date.
for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[Jeff Boyce]

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for the
entry. You'd need to use the exact same expression for every instance of
Date().

An alternate approach would be to create a small function that returns the
"category", based on two values passed in. One would use the current field,
and the other would be the [Enter an 'as-of' date] value.

 

[Guest]

I have replaced the statement with this:
Aging Bucket:
IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TRX_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_Date]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_date]>=[date:]-370,"181-360","360 Over")))))))
so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for the
entry. You'd need to use the exact same expression for every instance of
Date().

An alternate approach would be to create a small function that returns the
"category", based on two values passed in. One would use the current field,
and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input the date.
for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[Jeff Boyce]

Georgia

If Access says it's too complex, Access can't understand it -- incorrect may
not be the word to use, but you need Access to understand it for it to work!

You would need to be comfortable writing a small procedure/function before
you would take the second approach. If you aren't, here's another
possibility...

Create a query that returns all your fields. Add a new field into which you
type "[Date:]" (without the quotes). Access will add a name and you'll have
something like:

Expr1: [Date:]

Run this. It should prompt you for Date:. This was only a test.

Now create a second query, based on the first. In the second query, select
all the fields from the first query, then add your original IIF() statement.
In your original IIF(), replace the Date() with whatever Access called your
extra field in the first query (probably "Expr1"). You may need to place
brackets around it, as in "[Expr1]" (again, don't type the quotes).

When you run this second query, it knows it needs the first query, which
knows it needs to ask for [Date:].

Good luck

Jeff Boyce
<Access MVP>

I have replaced the statement with this:
Aging Bucket:

IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TR
X_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_D
ate]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_d
ate]>=[date:]-370,"181-360","360 Over")))))))

so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for the
entry. You'd need to use the exact same expression for every instance of
Date().

An alternate approach would be to create a small function that returns the
"category", based on two values passed in. One would use the current field,
and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input the date.
for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[John Spencer (MVP)]

Pardon me for jumping in, but I think that the problem may be that Date:
parameter needs to be defined as Date Type in the query.

Two ways to do this.

One:
CDate([Date

Two: Declare the parameter type
In SQL

Parameters [Date:] DateTime;
SELECT ....

In the query grid,
Right-Click in the grey space
Select Parameters
In the dialog type Date: in the Parameter column
Select Date/Time as Type
(above from memory, so it may not be totally correct as to names of columns and
names of types)

Georgia

If Access says it's too complex, Access can't understand it -- incorrect may
not be the word to use, but you need Access to understand it for it to work!

You would need to be comfortable writing a small procedure/function before
you would take the second approach. If you aren't, here's another
possibility...

Create a query that returns all your fields. Add a new field into which you
type "[Date:]" (without the quotes). Access will add a name and you'll have
something like:

Expr1: [Date:]

Run this. It should prompt you for Date:. This was only a test.

Now create a second query, based on the first. In the second query, select
all the fields from the first query, then add your original IIF() statement.
In your original IIF(), replace the Date() with whatever Access called your
extra field in the first query (probably "Expr1"). You may need to place
brackets around it, as in "[Expr1]" (again, don't type the quotes).

When you run this second query, it knows it needs the first query, which
knows it needs to ask for [Date:].

Good luck

Jeff Boyce
<Access MVP>

I have replaced the statement with this:
Aging Bucket:

IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TR
X_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_D
ate]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_d
ate]>=[date:]-370,"181-360","360 Over")))))))

so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for the
entry. You'd need to use the exact same expression for every instance of
Date().

An alternate approach would be to create a small function that returns the
"category", based on two values passed in. One would use the current field,
and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice
Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input the date.
for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[Guest]

THANK YOU BOTH!
IT WORKED WONDERFULLY!!!!!
THANK YOU THANK YOU!

Pardon me for jumping in, but I think that the problem may be that Date:
parameter needs to be defined as Date Type in the query.

Two ways to do this.

One:
CDate([Date

Two: Declare the parameter type
In SQL

Parameters [Date:] DateTime;
SELECT ....

In the query grid,
Right-Click in the grey space
Select Parameters
In the dialog type Date: in the Parameter column
Select Date/Time as Type
(above from memory, so it may not be totally correct as to names of columns and
names of types)

Georgia

If Access says it's too complex, Access can't understand it -- incorrect may
not be the word to use, but you need Access to understand it for it to work!

You would need to be comfortable writing a small procedure/function before
you would take the second approach. If you aren't, here's another
possibility...

Create a query that returns all your fields. Add a new field into which you
type "[Date:]" (without the quotes). Access will add a name and you'll have
something like:

Expr1: [Date:]

Run this. It should prompt you for Date:. This was only a test.

Now create a second query, based on the first. In the second query, select
all the fields from the first query, then add your original IIF() statement.
In your original IIF(), replace the Date() with whatever Access called your
extra field in the first query (probably "Expr1"). You may need to place
brackets around it, as in "[Expr1]" (again, don't type the quotes).

When you run this second query, it knows it needs the first query, which
knows it needs to ask for [Date:].

Good luck

Jeff Boyce
<Access MVP>

I have replaced the statement with this:
Aging Bucket:

IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TR
X_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_D
ate]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_d
ate]>=[date:]-370,"181-360","360 Over")))))))

so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!



:

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for the
entry. You'd need to use the exact same expression for every instance of
Date().

An alternate approach would be to create a small function that returns the
"category", based on two values passed in. One would use the current field,
and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice
Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input the date.
for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[Jeff Boyce]

Thanks, John! I'll have to remember to "type" parameters in queries (I
haven't needed to before).

Jeff

Pardon me for jumping in, but I think that the problem may be that Date:
parameter needs to be defined as Date Type in the query.

Two ways to do this.

One:
CDate([Date

Two: Declare the parameter type
In SQL

Parameters [Date:] DateTime;
SELECT ....

In the query grid,
Right-Click in the grey space
Select Parameters
In the dialog type Date: in the Parameter column
Select Date/Time as Type
(above from memory, so it may not be totally correct as to names of columns and
names of types)

Georgia

If Access says it's too complex, Access can't understand it -- incorrect may
not be the word to use, but you need Access to understand it for it to work!

You would need to be comfortable writing a small procedure/function before
you would take the second approach. If you aren't, here's another
possibility...

Create a query that returns all your fields. Add a new field into which you
type "[Date:]" (without the quotes). Access will add a name and you'll have
something like:

Expr1: [Date:]

Run this. It should prompt you for Date:. This was only a test.

Now create a second query, based on the first. In the second query, select
all the fields from the first query, then add your original IIF() statement.
In your original IIF(), replace the Date() with whatever Access called your
extra field in the first query (probably "Expr1"). You may need to place
brackets around it, as in "[Expr1]" (again, don't type the quotes).

When you run this second query, it knows it needs the first query, which
knows it needs to ask for [Date:].

Good luck

Jeff Boyce
<Access MVP>

I have replaced the statement with this:
Aging Bucket:

IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TR
X_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_D
ate]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_d
ate]>=[date:]-370,"181-360","360 Over")))))))

so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!



:

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to

do
this

in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask"

for
the

entry. You'd need to use the exact same expression for every

instance
of

Date().

An alternate approach would be to create a small function that

returns
the

"category", based on two values passed in. One would use the

current
field,

and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice
Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date? above
statement calculates the aging as of today, but i want to input

the
date.

for example if i want to age it as of 5/1/04 instead of manually putting
"#5/1/04#" in the statement.
Thank You!

 

[John Spencer (MVP)]

You don't always have to, but it helps. Access in most situations will figure
out the parameter type, but in some cases it cannot tell or it makes an
incorrect decision and instead of a date it gives you a division statement or it
gives you an error.

Thanks, John! I'll have to remember to "type" parameters in queries (I
haven't needed to before).

Jeff

Pardon me for jumping in, but I think that the problem may be that Date:
parameter needs to be defined as Date Type in the query.

Two ways to do this.

One:
CDate([Date

Two: Declare the parameter type
In SQL

Parameters [Date:] DateTime;
SELECT ....

In the query grid,
Right-Click in the grey space
Select Parameters
In the dialog type Date: in the Parameter column
Select Date/Time as Type
(above from memory, so it may not be totally correct as to names of columns and
names of types)

Georgia

If Access says it's too complex, Access can't understand it -- incorrect may
not be the word to use, but you need Access to understand it for it to work!

You would need to be comfortable writing a small procedure/function before
you would take the second approach. If you aren't, here's another
possibility...

Create a query that returns all your fields. Add a new field into which you
type "[Date:]" (without the quotes). Access will add a name and you'll have
something like:

Expr1: [Date:]

Run this. It should prompt you for Date:. This was only a test.

Now create a second query, based on the first. In the second query, select
all the fields from the first query, then add your original IIF() statement.
In your original IIF(), replace the Date() with whatever Access called your
extra field in the first query (probably "Expr1"). You may need to place
brackets around it, as in "[Expr1]" (again, don't type the quotes).

When you run this second query, it knows it needs the first query, which
knows it needs to ask for [Date:].

Good luck

Jeff Boyce
<Access MVP>

I have replaced the statement with this:
Aging Bucket:

IIf([TRX_Date]>=[date:]-40,"0-30",IIf([TRX_Date]>=[date:]-70,"31-60",IIf([TR
X_Date]>=[date:]-100,"61-90",IIf([TRX_Date]>=[date:]-130,"91-120",IIf([TRX_D
ate]>=[date:]-160,"121-150",IIf([TRX_date]>=[date:]-190,"151-180",IIf([TRX_d
ate]>=[date:]-370,"181-360","360 Over")))))))
so it will ask for the date.
however, the error message came up saying:
"This expression is typed incorrectly, or it is too complex to be
evaluated.

Is this statement incorrect?
The another approach you have mentioned, will you explain more in details?

Thank you!



:

Georgia

You've asked in a "queries" newsgroup, so I assume you are trying to do
this
in a query. If you replace the "Date()" with something like "[Enter an
'as-of' date]" (without the quotes), I suspect the query will "ask" for
the
entry. You'd need to use the exact same expression for every instance
of
Date().

An alternate approach would be to create a small function that returns
the
"category", based on two values passed in. One would use the current
field,
and the other would be the [Enter an 'as-of' date] value.

--
Good luck

Jeff Boyce
<Access MVP>

I have table that lists Invoice #, Invoice $, and Invoice Date.


Here is my IIF Statement:
Aging Bucket: IIf([Invoice Date]>=Date()-40,"0-30",IIf([Invoice
Date]>=Date()-70,"31-60",IIf([Invoice
Date]>=Date()-100,"61-90",IIf([Invoice
Date]>=Date()-130,"91-120",IIf([Invoice
Date]>=Date()-160,"121-150",IIf([Invoice
Date]>=Date()-190,"151-180",IIf([Invoice
Date]>=Date()-370,"181-360","360
Over")))))))

this works wonderfully.
my question is that how would it have it where I choose the date?
above
statement calculates the aging as of today, but i want to input the
date.
for example if i want to age it as of 5/1/04 instead of manually
putting
"#5/1/04#" in the statement.
Thank You!