Dateadd and conditional true false statement

[Todd]

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

 

[mikey014]

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"

 

[mikey014]

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.

 

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

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

 

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