Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form
From: Bob H on 16 May 2010 08:26
On 16/05/2010 12:31, Bob H wrote:
> 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

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
From: Bob H on 16 May 2010 09:21
On 16/05/2010 13:26, Bob H wrote:
> On 16/05/2010 12:31, Bob H wrote:
>> 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
>
> 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

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.
From: Bob Quintal on 16 May 2010 20:47
Bob H <bob(a)despammer.com> wrote in
news:OvqdnapLm6PLbHLWnZ2dnUVZ7q2dnZ2d(a)giganews.com:


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

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.


First  |  Prev  | 
Pages: 1 2 3 4
Prev: Wizard unable to open form in Form or Datasheet view
Next: Pivot table form