If problem-comparing field+1735<Today()

[Madmax]

Had another snag while trying to fill out the last bit of this it might be because I am trying to do this in VBA that I am having so much problems

Private Sub Security_Clearance_Closed_Date_AfterUpdate()
If IsDate(Me![Security Clearance Closed Date] + 1735 < Today()) = True Then
Me![Security Clearance Needed] = 1
Else
Me![Security Clearance Needed] = 0


End If

Also tried below,

Private Sub Security_Clearance_Closed_Date_AfterUpdate()
If Me![Security Clearance Closed Date] + 1735 < Today() = True Then
Me![Security Clearance Needed] = 1
Else
Me![Security Clearance Needed] = 0
End If



Basically, I want to know with a checkbox(which is a field in a table this time and also on the form) that the closed date for Sec Clearances isnt 4 years and 9 months ago(basically after 5 years its no good but I only am allowed 90 days at the end to fix it.)

So, lol, what did I screw up this time?

[boblarson]

Have you tried:

Private Sub Security_Clearance_Closed_Date_AfterUpdate()
If IsDate(Me![Security Clearance Closed Date]) Then
   If DateAdd("d", 1735, [Security Clearance Closed Date]) <  Date Then
       Me![Security Clearance Needed] = 1
   Else
       Me![Security Clearance Needed] = 0
   End If
End If

[Madmax]

Private Sub Security_Clearance_Closed_Date_AfterUpdate()
If IsDate(Me![Security Clearance Closed Date]) Then
If DateAdd("d", 1735, [Security Clearance Closed Date]) < Date Then
Me![Security Clearance Needed] = 1
Else
Me![Security Clearance Needed] = 0
End If
End If

About to go try this but just for reference this is what I am reading

If the date is there then is this date+1735 less than today, If so then the field is 1, If not then it is 0

End

Is that it literally? the reason I ask when you pull isdate up it not only gives you if there is a date but it pulls the date into that varible? is that right?

Just curious.

[boblarson]

IsDate just checks to see if it is a date. If it is null it wouldn't be a date. There is no variable.

That first part to check to see if it is a date is just to make sure it isn't null. If it is then it won't do anything. If it is a date it moves on to the next line and then it checks to see if the Security Clearance Closed Date PLUS 1735 days is less than DATE (which is what Access uses for the current date) and if it is less than today (meaning it has already passed) then it would set it to 1 otherwise it would do 0. If the [Security Clearance Needed] is really a true/false field then in Access it should really be -1 or 0 not 1 or 0.

[Madmax]

lol all my if's are 1 and 0 guess I know what I will be doing for the next hour. is -1 yes then? Thanks for the other btw, I see what you are talking about which makes since. dont want to run if nothing their to run it.

Dateadd(Type,amount,Field)

Is that the currect use of dateadd then?

Is there a quick reference listing out there for common used functions and there currect setup? I kind of feel bad taking up your time.

[boblarson]

For a reference try going to the VBA window and type DateAdd and make sure your cursor is in it and hit F1.

And yes, the DateAdd is what you said it is. You can use a specific date with it too by using:

DateAdd("d", -7, #3/24/2011#)

Which would return 3/17/2011.

The parts for date Add (and others like DateDiff) are

"d" - days
"m" - months
"yyyy" - years
"q" - quarters (3 months)
"ww" - weeks
"h" - hours
"n" - minutes (not intuitive because you'd think it would be m instead)
"s" seconds

[Madmax]

Thanks for all your help today, About to head to urgent care and I have tomorrow off but probable tag the site this weekend some because I ussually through time towards work when I get bored around the house. Have a great weekend,

TSgt Maxwell