Can't get the logic right

[Mr. Clean]

I have this code:

If ((DateValue(TB)) >= Now()) And _
((DateValue(TB) + 365) <= ((Now()) + 365)) Then

 

[Don Guillett]

If TB is a valid date,try this

Sub dateit()
If [TB] >= Date And [TB] < Date + 365 Then MsgBox "Hi"
End Sub

 

[John Wilson]

Mr. Clean,

You don't need all those extra parenthesis.
Also, in VBA you can use "NOW" instead of "NOW()"
Also, you should convert NOW to an Integer.
If TB = 37983 and NOW = 37983.43357, your

= will never work for today's date.

The following seemed to work for me, logic wise.

Sub TestMe()
Dim TB As String
TB = "12/28/03"
If DateValue(TB) >= Int(Now) And _
DateValue(TB) <= Int(Now) + 365 Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

John

 

[Harald Staff]

Hi John

There's an equivalent for Int(Now) called Date:

If DateValue(TB) >= Date And _

Best wishes Harald
Followup to newsgroup only please

 

[John Wilson]

Harald,

Remembered that as soon as I saw Don's reply.
Am just so in the habit of using Int(Now) in my own
apps that I completely forgot about it.
Although both ways work, "Date" would be a better
alternative.

Thanks,
John

 

[Ron Rosenfeld]

I have this code:

If ((DateValue(TB)) >= Now()) And _
((DateValue(TB) + 365) <= ((Now()) + 365)) Then
.
.
End If


And it isn't getting me only the values of TB that are within one year
of today's date. What do I need to change to get that?

How about:

If DateValue(TB) >= Date And _
(DateValue(TB) - Date) < 365 Then


--ron

 

[Harald Staff]

No criticism intended, John, and I'm not sure there's a significant gain of
speed. Just added a little info for our regular readers

 

[Dana DeLouis]

Just to be different...adding 365 days may be an attempt to determine if
something is within 1 year. Adding 365 may not work over a leap year. This
idea is not tested to well, but just an idea...

Sub Demo()
Dim dte As Date
dte = #12/30/2004#

Select Case dte
Case Date To DateAdd("yyyy", 1, Date)
MsgBox "Within 1 year"
Case 0 To Date - 1
MsgBox "Happened in Past!"
Case Else
MsgBox "More than 1 year in the future"
End Select

End Sub

 

[Guest]

This discussion of 365, is something that is also not in the best interest of the "programming" community. Anyone seem to remember the whole discussion of Y2K? Some of that was because programmers did not correctly forsee or plan for things like changes in dates. You may not realize it yet, but come Feb 29th of the coming year, you may realize that 2004 is a leap year, and there are in fact 366 days. Now in some cases 365 may suffice for the intent of the program, but since March 1 of this last year, a simple sum of 365, would have lost a day...

So building on the code by John Wilson with the suggestion of Harald Staff to use Date instead of Int(Now), you can add one year (the typical result of adding 365 days) by doing the following:


Sub TestMe()
Dim TB As String
TB = "12/28/03"
If DateValue(TB) >= Date And _
DateValue(TB) <= DateAdd("yyyy", 1, Date) Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

Excel/VBA will account for the 366 days. Of course you could write your own date calculating function to determine if the year is a leap year or not, but then you would have to reference the records to determine when a typically determined leap year, is not in fact a leap year. There was some discussion that 2000 was not going to be a leap year, however some time "authorities" determined that 2000 would in fact be a leap year, as it would upset the continuum *Rolling eyes* or something like that.

 

[John Wilson]

Harald,

No criticism intended, John

I didn't take offense to your reply at all.
It's something I should have remembered (seeing as it's
not the first time that I've been reminded about it).
There probably isn't a significant gain in speed (although
some might argue that point), but if someone is only
interested in the date, it's a lot cleaner to use it as
opposed to Int(Now).

Just added a little info for our regular readers

Including me...and maybe I'll remember it the next time <g>

Thnaks,
John