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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
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")) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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") |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|