Bob H wrote in
:

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

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


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(NextTestDateDate(),"OverDue",iif((NextTestDat e-date()7),"Due
Soon","OK"))
works for me in immediate mode