out of date items

B

Bob H

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
 
P

PieterLinden via AccessMonster.com

Bob said:
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
Click to expand...

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.
 
P

PieterLinden via AccessMonster.com

Bob said:
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
Click to expand...

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

Out of date: NextTestDate<Date()
 
B

Bob H

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

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
 
B

Bob H

Bob said:
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
Click to expand...

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

Out of date: NextTestDate<Date()
Click to expand...

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
 
B

Bob Quintal

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
Click to expand...

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

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

Bob H

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

iif( NextTestDate< date(),"OverDue",iif(date()<= NextTestDate +7,"Due
Soon","OK"))
Click to expand...


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

+operator, in an expression without a corresponding operand
 
B

Bob Quintal

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

+operator, in an expression without a corresponding operand
Click to expand...

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")
 
B

Bob Quintal

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")
Click to expand...
oops, change mydate to NextTestDate in both places.
 
B

Bob H

Post the SQL, because it worked on my machine.
Click to expand...

I can't post the SQL because after I copied and pasted the line you
provided, with a change of Date name, I continually get the invalid
syntax error message and Access won't let me change to the SQL window.

This is thew line I have in Design View in the OutOfDate field:

OutOfDate:iif(
NextTestDate<Date(),"OverDue",iif((NextTestDate-Date()<7),"Due Soon","OK")

Thanks
 
B

Bob H

Post the SQL, because it worked on my machine.
Click to expand...

Using this part of the line:

OutOfDate: IIf([NextTestDate]<Date(),"OverDue")<< I added the closing
bracket here

the query runs ok with no error messages

SQL:
SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited, IIf([NextTestDate]<Date(),"OverDue") AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


But if I add the following, and removing the closing bracket I added

,iif((myDate-date()<7),"Due Soon","OK")

I get continual syntax error messages, and Access 2007 won't let me do
anything else.

Thanks
 
B

Bob Quintal

Post the SQL, because it worked on my machine.
Click to expand...

Using this part of the line:

OutOfDate: IIf([NextTestDate]<Date(),"OverDue")<< I added the
closing bracket here

the query runs ok with no error messages

SQL:
SELECT tblTools.ToolTypeID, tblTools.Manufacturer,
tblTools.Product, tblTools.Size, tblTools.[Lenght Size],
tblTools.SWL, tblTools.Drive, tblTools.Range, tblTools.Increment,
tblTools.ManufSerialNo, tblTools.SerialNo, tblTools.AssetNo,
tblTools.MPSENo, tblTools.LastTestDate,
DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS NextTestDate,
tblTools.CertificateNo, tblTools.LocationID, tblTools.Notes,
tblTools.Calibrate, tblTools.DateAdded, tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue") AS OutOfDate FROM tblToolType
INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID WHERE
(((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


But if I add the following, and removing the closing bracket I
added

,iif((myDate-date()<7),"Due Soon","OK")

I get continual syntax error messages, and Access 2007 won't let
me do anything else.

Thanks
Click to expand...

try
,iif((NextTestDate-date())<7,"Due Soon","OK")
note: moved the parenthesis from after the seven to before the <

IIf(NextTestDate<Date(),"OverDue",iif((NextTestDate-date()<7),"Due
Soon","OK"))
works for me in immediate mode
 
B

Bob H

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


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

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


Post the SQL, because it worked on my machine.
Click to expand...

Using this part of the line:

OutOfDate: IIf([NextTestDate]<Date(),"OverDue")<< I added the
closing bracket here

the query runs ok with no error messages

SQL:
SELECT tblTools.ToolTypeID, tblTools.Manufacturer,
tblTools.Product, tblTools.Size, tblTools.[Lenght Size],
tblTools.SWL, tblTools.Drive, tblTools.Range, tblTools.Increment,
tblTools.ManufSerialNo, tblTools.SerialNo, tblTools.AssetNo,
tblTools.MPSENo, tblTools.LastTestDate,
DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS NextTestDate,
tblTools.CertificateNo, tblTools.LocationID, tblTools.Notes,
tblTools.Calibrate, tblTools.DateAdded, tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue") AS OutOfDate FROM tblToolType
INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID WHERE
(((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


But if I add the following, and removing the closing bracket I
added

,iif((myDate-date()<7),"Due Soon","OK")

I get continual syntax error messages, and Access 2007 won't let
me do anything else.

Thanks
Click to expand...

try
,iif((NextTestDate-date())<7,"Due Soon","OK")
note: moved the parenthesis from after the seven to before the<

IIf(NextTestDate<Date(),"OverDue",iif((NextTestDate-date()<7),"Due
Soon","OK"))
works for me in immediate mode
Click to expand...
Thanks, that works ok now for me.
Now I just need to have something that will differentiate between items
which have an Overdue test date, because there is a next test date in
the field, and those items where the test date field is blank. Presently
in the OutOfDate column, those items are being given an OK status.
Removing the OK from the line, leaves that status as blank.

Thanks for your help
 
B

Bob H

On 16/05/2010 00:31, Bob Quintal wrote:

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


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

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


Post the SQL, because it worked on my machine.

Using this part of the line:

OutOfDate: IIf([NextTestDate]<Date(),"OverDue")<< I added the
closing bracket here

the query runs ok with no error messages

SQL:
SELECT tblTools.ToolTypeID, tblTools.Manufacturer,
tblTools.Product, tblTools.Size, tblTools.[Lenght Size],
tblTools.SWL, tblTools.Drive, tblTools.Range, tblTools.Increment,
tblTools.ManufSerialNo, tblTools.SerialNo, tblTools.AssetNo,
tblTools.MPSENo, tblTools.LastTestDate,
DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS NextTestDate,
tblTools.CertificateNo, tblTools.LocationID, tblTools.Notes,
tblTools.Calibrate, tblTools.DateAdded, tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue") AS OutOfDate FROM tblToolType
INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID WHERE
(((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


But if I add the following, and removing the closing bracket I
added

,iif((myDate-date()<7),"Due Soon","OK")

I get continual syntax error messages, and Access 2007 won't let
me do anything else.

Thanks
Click to expand...

try
,iif((NextTestDate-date())<7,"Due Soon","OK")
note: moved the parenthesis from after the seven to before the<

IIf(NextTestDate<Date(),"OverDue",iif((NextTestDate-date()<7),"Due
Soon","OK"))
works for me in immediate mode
Click to expand...
Thanks, that works ok now for me.
Now I just need to have something that will differentiate between items
which have an Overdue test date, because there is a next test date in
the field, and those items where the test date field is blank. Presently
in the OutOfDate column, those items are being given an OK status.
Removing the OK from the line, leaves that status as blank.

Thanks for your help
Click to expand...

I have been trying the Nz function like this : Nz([NextTestDate],Date())
but Access 2007 keeps telling me that there is missing closing
parenthesis or vertical bar, but when I add the vertical bar, it is invalid.

Thanks
 
B

Bob H

On 16/05/2010 00:31, Bob Quintal wrote:

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


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

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


Post the SQL, because it worked on my machine.

Using this part of the line:

OutOfDate: IIf([NextTestDate]<Date(),"OverDue")<< I added the
closing bracket here

the query runs ok with no error messages

SQL:
SELECT tblTools.ToolTypeID, tblTools.Manufacturer,
tblTools.Product, tblTools.Size, tblTools.[Lenght Size],
tblTools.SWL, tblTools.Drive, tblTools.Range, tblTools.Increment,
tblTools.ManufSerialNo, tblTools.SerialNo, tblTools.AssetNo,
tblTools.MPSENo, tblTools.LastTestDate,
DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS NextTestDate,
tblTools.CertificateNo, tblTools.LocationID, tblTools.Notes,
tblTools.Calibrate, tblTools.DateAdded, tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue") AS OutOfDate FROM tblToolType
INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID WHERE
(((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


But if I add the following, and removing the closing bracket I
added

,iif((myDate-date()<7),"Due Soon","OK")

I get continual syntax error messages, and Access 2007 won't let
me do anything else.

Thanks

try
,iif((NextTestDate-date())<7,"Due Soon","OK")
note: moved the parenthesis from after the seven to before the<

IIf(NextTestDate<Date(),"OverDue",iif((NextTestDate-date()<7),"Due
Soon","OK"))
works for me in immediate mode
Click to expand...
Thanks, that works ok now for me.
Now I just need to have something that will differentiate between items
which have an Overdue test date, because there is a next test date in
the field, and those items where the test date field is blank. Presently
in the OutOfDate column, those items are being given an OK status.
Removing the OK from the line, leaves that status as blank.

Thanks for your help
Click to expand...

I have been trying the Nz function like this : Nz([NextTestDate],Date())
but Access 2007 keeps telling me that there is missing closing
parenthesis or vertical bar, but when I add the vertical bar, it is
invalid.

Thanks
Click to expand...

Update,
ok, I think I have what I want now, as I have been playing around with
different things etc, and have this:
OutOfDate:
IIf([NextTestDate<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date")))

So where there is a blank field for the NTD the Nz function says No Test
Date.
Where the Test date is passed, then Overdue
But when Test Date is current or in the future, then that Test Date is
entered into the adjacent field.

Does it matter what order the SQL is in, ie should Nz be first, or is it
ok where it is.

Thanks again for you assistance.
 
B

Bob Quintal

Update,
ok, I think I have what I want now, as I have been playing around
with different things etc, and have this:
OutOfDate:
IIf([NextTestDate<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"D
ue Soon",Nz([NextTestDate],"No Test Date")))

So where there is a blank field for the NTD the Nz function says
No Test Date.
Where the Test date is passed, then Overdue
But when Test Date is current or in the future, then that Test
Date is entered into the adjacent field.

Does it matter what order the SQL is in, ie should Nz be first, or
is it ok where it is.

Thanks again for you assistance.
Click to expand...

Order is important only when cases overlap such as your first and
second cases, with the nz(), if the results meet your needs, it's where
it shoould be.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can this be done 2
selective Next test Date ? 1
Query wants parameter value 4
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Mail Merge Help 1
Display weeks from a Milestone 0
How to bulk add section breaks after a particular combination of letters/numbers 0
Work Budget Salary vs Real Time Salary 0

Top