Problem with calculated results

[Greg]

I am using this calculation in a query to find invoices that are less then
30 days, over 30, over 60, over 90, and over 120.

IIf((Now()-AR6_OpenInvoice 1.INV_DATE)\30> 4,4,(Now()-AR6_OpenInvoice 1.INV_DAT
E)\30);


The problem is the invoice was created on let say 9/17/03 and is less then
30 days old but the calculation looks at it and says it is over 30. What do
I need to fix this problem?

Thanks agin for all the help!!

Greg

[Fredg]

Re: Problem with calculated results

It's quite possible, using Now() that, depending upon the Inv_Date and the
exact time of day the query was run, you may miss what you think should be
there. In other words, if you run the query at 8 AM, you may return more (or
fewer) records than if you run the query at 11 PM.

Now() carries a time of day value as well as the date.
Unless you are looking for records exactly 30 days to the second of when the
query is run, use Date() instead.

Also, unless the query has more than one table with the same Field Name
(Inv_Date), you don't need the table name qualifier.
If the logic of your expression is correct, try:

IIf((Date() - [INV_DATE])\30>4,4,(Date( ) - [INV_DATE])\30)

Or... why not make a new Module:

Function InvoiceAge(Date In as Date)
Dim intElapsedDays as integer
Dim intResult as integer
intElapsedDays = DateDiff("d",Da teIn,Date())

Select Case intElapsedDays
Case 1 to 29
intResult = 1
Case 30 to 59
intResult = 30
Case 60 to 89
intResult = 60
Case 90 to 119
untResult = 90
Case Else
intResult = 120
End Select
InvoiceAge = intResult
End Function
===============
Change the time periods and the expected results to suit yourself.


Call the function from a query:
Aged: InvoiceAge([Inv_Date])
Sort by this field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


"Greg" <gknapper@itlan .net> wrote in message
news:AfAjb.1726 0$zw4.3268@nwrd ny01.gnilink.ne t...[color=blue]
> I am using this calculation in a query to find invoices that are less then
> 30 days, over 30, over 60, over 90, and over 120.
>
>[/color]
IIf((Now()-AR6_OpenInvoice 1.INV_DATE)\30> 4,4,(Now()-AR6_OpenInvoice 1.INV_DAT[color=blue]
> E)\30);
>
>
> The problem is the invoice was created on let say 9/17/03 and is less then
> 30 days old but the calculation looks at it and says it is over 30. What[/color]
do[color=blue]
> I need to fix this problem?
>
> Thanks agin for all the help!!
>
> Greg
>
>[/color]