Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form
From: Bob Quintal on 15 May 2010 19:31
Bob H <bob(a)despammer.com> wrote in
news:HqGdnYMaIufza3PWnZ2dnUVZ7rSdnZ2d(a)giganews.com:

> On 15/05/2010 19:58, Bob Quintal wrote:
>> Bob Quintal<rquintal(a)sPAmpatico.ca> wrote in
>> news:Xns9D79975675D5BQuintal(a)69.16.185.250:
>>
>>> Bob H<bob(a)despammer.com> wrote in
>>> news:Fu2dnTRYIpAGQXPWnZ2dnUVZ8qadnZ2d(a)giganews.com:
>>>
>>>> On 15/05/2010 17:21, Bob Quintal wrote:
>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>> news:LNGdnXHUxNVHKnPWnZ2dnUVZ7vqdnZ2d(a)giganews.com:
>>>>>
>>>>>> 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.
From: Bob H on 16 May 2010 05:02
On 16/05/2010 00:31, Bob Quintal wrote:
> Bob H<bob(a)despammer.com> wrote in
> news:HqGdnYMaIufza3PWnZ2dnUVZ7rSdnZ2d(a)giganews.com:
>
>> On 15/05/2010 19:58, Bob Quintal wrote:
>>> Bob Quintal<rquintal(a)sPAmpatico.ca> wrote in
>>> news:Xns9D79975675D5BQuintal(a)69.16.185.250:
>>>
>>>> Bob H<bob(a)despammer.com> wrote in
>>>> news:Fu2dnTRYIpAGQXPWnZ2dnUVZ8qadnZ2d(a)giganews.com:
>>>>
>>>>> On 15/05/2010 17:21, Bob Quintal wrote:
>>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>>> news:LNGdnXHUxNVHKnPWnZ2dnUVZ7vqdnZ2d(a)giganews.com:
>>>>>>
>>>>>>> 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.

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
From: Bob H on 16 May 2010 06:08
On 16/05/2010 00:31, Bob Quintal wrote:
> Bob H<bob(a)despammer.com> wrote in
> news:HqGdnYMaIufza3PWnZ2dnUVZ7rSdnZ2d(a)giganews.com:
>
>> On 15/05/2010 19:58, Bob Quintal wrote:
>>> Bob Quintal<rquintal(a)sPAmpatico.ca> wrote in
>>> news:Xns9D79975675D5BQuintal(a)69.16.185.250:
>>>
>>>> Bob H<bob(a)despammer.com> wrote in
>>>> news:Fu2dnTRYIpAGQXPWnZ2dnUVZ8qadnZ2d(a)giganews.com:
>>>>
>>>>> On 15/05/2010 17:21, Bob Quintal wrote:
>>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>>> news:LNGdnXHUxNVHKnPWnZ2dnUVZ7vqdnZ2d(a)giganews.com:
>>>>>>
>>>>>>> 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
From: Bob Quintal on 16 May 2010 06:57
Bob H <bob(a)despammer.com> wrote in
news:g_KdnU8KGrS7WXLWnZ2dnUVZ7oSdnZ2d(a)giganews.com:

> On 16/05/2010 00:31, Bob Quintal wrote:
>> Bob H<bob(a)despammer.com> wrote in
>> news:HqGdnYMaIufza3PWnZ2dnUVZ7rSdnZ2d(a)giganews.com:
>>
>>> On 15/05/2010 19:58, Bob Quintal wrote:
>>>> Bob Quintal<rquintal(a)sPAmpatico.ca> wrote in
>>>> news:Xns9D79975675D5BQuintal(a)69.16.185.250:
>>>>
>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>> news:Fu2dnTRYIpAGQXPWnZ2dnUVZ8qadnZ2d(a)giganews.com:
>>>>>
>>>>>> On 15/05/2010 17:21, Bob Quintal wrote:
>>>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>>>> news:LNGdnXHUxNVHKnPWnZ2dnUVZ7vqdnZ2d(a)giganews.com:
>>>>>>>
>>>>>>>> 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


From: Bob H on 16 May 2010 07:31
On 16/05/2010 11:57, Bob Quintal wrote:
> Bob H<bob(a)despammer.com> wrote in
> news:g_KdnU8KGrS7WXLWnZ2dnUVZ7oSdnZ2d(a)giganews.com:
>
>> On 16/05/2010 00:31, Bob Quintal wrote:
>>> Bob H<bob(a)despammer.com> wrote in
>>> news:HqGdnYMaIufza3PWnZ2dnUVZ7rSdnZ2d(a)giganews.com:
>>>
>>>> On 15/05/2010 19:58, Bob Quintal wrote:
>>>>> Bob Quintal<rquintal(a)sPAmpatico.ca> wrote in
>>>>> news:Xns9D79975675D5BQuintal(a)69.16.185.250:
>>>>>
>>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>>> news:Fu2dnTRYIpAGQXPWnZ2dnUVZ8qadnZ2d(a)giganews.com:
>>>>>>
>>>>>>> On 15/05/2010 17:21, Bob Quintal wrote:
>>>>>>>> Bob H<bob(a)despammer.com> wrote in
>>>>>>>> news:LNGdnXHUxNVHKnPWnZ2dnUVZ7vqdnZ2d(a)giganews.com:
>>>>>>>>
>>>>>>>>> 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
>
>
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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form