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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|