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>
GEORGIA said:
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!