Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form
From: Bob H on 15 May 2010 05:01
I have a tools database with LastTestDate and NextTestDate fields, which
is conditionally formatted for approaching NextTestDate.

What I want to do now, is to run a query or report which tells me which
items are out of date.

I have a query which give me the NextTestDate:
NextTestDate: DateAdd([PeriodTypeID],[Freq],[LastTestDate])

So if the NextTestDate is, say today, then I would like to be able to
run a query which will sort out the items which are either out of date
or will be very soon.

Thanks
From: PieterLinden via AccessMonster.com on 15 May 2010 07:35
Bob H wrote:
>I have a tools database with LastTestDate and NextTestDate fields, which
>is conditionally formatted for approaching NextTestDate.
>
>What I want to do now, is to run a query or report which tells me which
>items are out of date.
>
>I have a query which give me the NextTestDate:
>NextTestDate: DateAdd([PeriodTypeID],[Freq],[LastTestDate])
>
>So if the NextTestDate is, say today, then I would like to be able to
>run a query which will sort out the items which are either out of date
>or will be very soon.
>
>Thanks

Compare NextTestDate and the current date, Date(). Something along the lines
of

IIF(DateDiff("d", NextTestDate, Date())<=7,"Out of Date Soon","OKAY")

then sort or filter your dataset by this expression.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

From: PieterLinden via AccessMonster.com on 15 May 2010 07:36
Bob H wrote:
>I have a tools database with LastTestDate and NextTestDate fields, which
>is conditionally formatted for approaching NextTestDate.
>
>What I want to do now, is to run a query or report which tells me which
>items are out of date.
>
>I have a query which give me the NextTestDate:
>NextTestDate: DateAdd([PeriodTypeID],[Freq],[LastTestDate])
>
>So if the NextTestDate is, say today, then I would like to be able to
>run a query which will sort out the items which are either out of date
>or will be very soon.
>
>Thanks

Just compare NextTestDate to the current date, which is Date(), then return
an appropriate message or just filter.

Out of date: NextTestDate<Date()

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

From: Bob H on 15 May 2010 11:03
On 15/05/2010 12:35, PieterLinden via AccessMonster.com wrote:
> IIF(DateDiff("d", NextTestDate, Date())<=7,"Out of Date Soon","OKAY")

This works ok, but for items that are well out of date, or past the
NextTestDate, they were shown as OKAY, So I removed that and now those
said items are showing as blank in an OutOfDate field I am using.

Is there a way of differentiating between items that are OKAY and those
that are out of date.

Thanks
From: Bob H on 15 May 2010 11:05
On 15/05/2010 12:36, PieterLinden via AccessMonster.com wrote:
> Bob H wrote:
>> I have a tools database with LastTestDate and NextTestDate fields, which
>> is conditionally formatted for approaching NextTestDate.
>>
>> What I want to do now, is to run a query or report which tells me which
>> items are out of date.
>>
>> I have a query which give me the NextTestDate:
>> NextTestDate: DateAdd([PeriodTypeID],[Freq],[LastTestDate])
>>
>> So if the NextTestDate is, say today, then I would like to be able to
>> run a query which will sort out the items which are either out of date
>> or will be very soon.
>>
>> Thanks
>
> Just compare NextTestDate to the current date, which is Date(), then return
> an appropriate message or just filter.
>
> Out of date: NextTestDate<Date()
>

This gives -1 for out of date or 0 if in date.
I would like a more meaningful wording if it can be done.

Thanks

 |  Next  |  Last
Pages: 1 2 3 4
Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form