A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

out of date items



 
 
Thread Tools Display Modes
  #11  
Old May 16th, 2010, 12:31 AM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

Bob H 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.
Ads
  #12  
Old May 16th, 2010, 10:02 AM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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.


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(
NextTestDateDate(),"OverDue",iif((NextTestDate-Date()7),"Due Soon","OK")

Thanks
  #13  
Old May 16th, 2010, 11:08 AM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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
  #14  
Old May 16th, 2010, 11:57 AM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

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


  #15  
Old May 16th, 2010, 12:31 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

On 16/05/2010 11:57, Bob Quintal wrote:
Bob 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


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
  #16  
Old May 16th, 2010, 01:26 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

On 16/05/2010 12:31, Bob H wrote:
On 16/05/2010 11:57, Bob Quintal wrote:
Bob 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


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
  #17  
Old May 16th, 2010, 02:21 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default out of date items

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


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([NextTestDateDate(),"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.
  #18  
Old May 17th, 2010, 01:47 AM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default out of date items

Bob H wrote in
:



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([NextTestDateDate(),"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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.