 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2008Dateadd and conditional true false statement
I have a field called next monitor where I have another field called over due. In the overdue field, I am using the following code to make a yes/no determination. overdue: Iff(dateadd("m", [audit_len], [NextMon])<date()"yes","no") The audit_len is a field that has different numbers for the lenght of an audit. I also have a field called Audit_Date. This field is where I put the date of the last audit, and then the Next_mon calculates when the next audit is due. From the nextmon field, I use this data to determine if this audit is overdue with the code listed above. The issue is, when I put in a date like 10 Jan 06 in the Audit_Date field, Nextmon calculates 10 Jan 07. This returns a true statement. Good to go here. When I put in a date in the Audit_Date field of anything in 07, and the NextMon is calculated into 08, prior to todays date, the overdue formula is returning no, or false. Not sure what is wrong with the code for this error. I have used this code on three different Queries for forms, and all the same issue. Any ideas on how to make this run correctly. Thanks Todd I tried this variation of your code in PrimalScript vbs and it worked: audit_len = 12 NextMon = #12/31/2006# If DateAdd("m", audit_len, NextMon) < Date() Then sString = "Yes" Else sString = "No" End If WScript.Echo "OverDue: " & sString Returned "OverDue: Yes" If this is how you want the code to work, maybe you can simplify it like this. I noticed there appears to be a comma missing between date() and "Yes" > I have a field called next monitor where I have another field called over due. > In the overdue field, I am using the following code to make a yes/no [quoted text clipped - 22 lines] > > Todd Mike Thanks for the code. I tried it in my query, and it did not work. Not sure if I mentioned that this is a query. The query told me about some bad syntax. Thanks for the suggestion. I may have to move this block to a form, and the above code will work. Thanks Todd > I tried this variation of your code in PrimalScript vbs and it worked: > [quoted text clipped - 40 lines] > > > > Todd I misunderstood how you were using this so my code variation won't work. I thought you were displaying it on the form or report using code on the On Current or Detail section's On Format event. It might just be easier to do it that way rather than putting it right in the query. > I have a field called next monitor where I have another field called over due. > In the overdue field, I am using the following code to make a yes/no [quoted text clipped - 22 lines] > > Todd I just figured it out. My formula was actually adding the audit length to the date of when it was due, so it was looking into the future. When I put the right field for it to properly complete the expression, then when todays date is past, it will give me the yes. Thanks Todd > I misunderstood how you were using this so my code variation won't work. I > thought you were displaying it on the form or report using code on the On [quoted text clipped - 27 lines] > > > > Todd |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.