A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

out of date items



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2010, 10:01 AM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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
  #2  
Old May 15th, 2010, 12:35 PM posted to microsoft.public.access.forms
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default out of date items

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/For...forms/201005/1

  #3  
Old May 15th, 2010, 12:36 PM posted to microsoft.public.access.forms
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default out of date items

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: NextTestDateDate()

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

  #4  
Old May 15th, 2010, 04:03 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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
  #5  
Old May 15th, 2010, 04:05 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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: NextTestDateDate()


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

  #6  
Old May 15th, 2010, 05:21 PM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

Bob H wrote in
:

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


First test for "Past Due", then your /Soon Due:.

iif( NextTestDate date(),"OverDue",iif(date() = NextTestDate +7,"Due
Soon","OK"))


  #7  
Old May 15th, 2010, 06:40 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

On 15/05/2010 17:21, Bob Quintal wrote:
Bob wrote in
:

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


First test for "Past Due", then your /Soon Due:.

iif( NextTestDate date(),"OverDue",iif(date()= NextTestDate +7,"Due
Soon","OK"))




Thanks , but I am getting missing operand error, or more precisely

+operator, in an expression without a corresponding operand
  #8  
Old May 15th, 2010, 07:52 PM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

Bob H wrote in
:

On 15/05/2010 17:21, Bob Quintal wrote:
Bob wrote in
:

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


First test for "Past Due", then your /Soon Due:.

iif( NextTestDate date(),"OverDue",iif(date()= NextTestDate
+7,"Due Soon","OK"))




Thanks , but I am getting missing operand error, or more precisely

+operator, in an expression without a corresponding operand


sorry forgot some parentheses, Access was calculating the difference
between the dates, returning true or false, then adding 7.

iif( mydate date(),"OverDue",iif((myDate-date()7),"Due Soon","OK")
  #9  
Old May 15th, 2010, 07:58 PM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

Bob Quintal wrote in
:

Bob H wrote in
:

On 15/05/2010 17:21, Bob Quintal wrote:
Bob wrote in
:

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

First test for "Past Due", then your /Soon Due:.

iif( NextTestDate date(),"OverDue",iif(date()= NextTestDate
+7,"Due Soon","OK"))




Thanks , but I am getting missing operand error, or more precisely

+operator, in an expression without a corresponding operand


sorry forgot some parentheses, Access was calculating the

difference
between the dates, returning true or false, then adding 7.

iif( mydate date(),"OverDue",iif((myDate-date()7),"Due

Soon","OK")

oops, change mydate to NextTestDate in both places.
  #10  
Old May 15th, 2010, 08:30 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

On 15/05/2010 19:58, Bob Quintal wrote:
Bob wrote in
:

Bob wrote in
:

On 15/05/2010 17:21, Bob Quintal wrote:
Bob wrote in
:

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

First test for "Past Due", then your /Soon Due:.

iif( NextTestDate date(),"OverDue",iif(date()= NextTestDate
+7,"Due Soon","OK"))




Thanks , but I am getting missing operand error, or more precisely

+operator, in an expression without a corresponding operand


sorry forgot some parentheses, Access was calculating the

difference
between the dates, returning true or false, then adding 7.

iif( mydate date(),"OverDue",iif((myDate-date()7),"Due

Soon","OK")

oops, change mydate to NextTestDate in both places.


Thanks, but now getting invalid syntax error message:
You may have entered an operand without an operator
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.