Greater then todays date code?

[bacarat]

Hi Guys,
I am trying to write some VB in the [Status] Table. Not sure how to format this. When [Mytable]= 4 months or greater from todays date. I would like it to return "Expired" when it is 3 months and 29 days greater or less than todays date i would like it to return "Good". Can anyone help me write the code for this? Thanks in advance!

 

[rainman89]

you are looking for the dateadd function

dateadd("mm", 4,[yourdatefield])

 

[bacarat]

How can i return a value of "Expired" or "Ok"??

 

[rainman89]

iff (dateadd("mm", 4,[yourdatefield])>Date(),"Expired","good")

 

[bacarat]

Awesome thanks! One more question, how would I format the code if the date field is empty to return "Expired"?

 

[rainman89]

=iff (isnull([datefield]),"Expired",(iff (dateadd("mm", 4,[yourdatefield])>Date(),"Expired","good")))

 

[bacarat]

I am getting some errors when entering this, sorry im new to this am i entering this wrong? I replaced the named with my fields, getting an expected: end of statement error.

Private Sub Form_Current()
=iff (isnull([datefield]),"Expired",(iff (dateadd("mm", 4,[yourdatefield])>Date(),"Expired","good")))
End Sub

 

[rainman89]

you would need
me.yourstatusboxname= iff (isnull([datefield]),"Expired",(iff (dateadd("mm", 4,[yourdatefield])>Date(),"Expired","good")))

 

[bacarat]

Getting a compile error in the second iif statment.
Sub or Function not defined?

 

[CraigDolphin]

me.yourstatusboxname= IIF (isnull([datefield]),"Expired",(IIF (dateadd("mm", 4,[yourdatefield])>Date(),"Expired","good")))

 

[bacarat]

Working now, however im getting an invalid procedure or argument error on the entries with dates in them? Null values work?

 

[CraigDolphin]

me.yourstatusboxname= IIF(isnull([datefield]),"Expired",(IIF(dateadd("m", 4,[yourdatefield])>Date(),"Expired","Good")))

didn't notice the double-m before.

 

[bacarat]

Works! Was just about to remove the "mm" too. Thanks Again!

 

[rainman89]

didnt notice i'm an idiot and dont even know my function names..... sorry bacarat!

 

[CraigDolphin]

We all have our moments Ray. Don't sweat it It's an easy typo to make!