Not Returning Correct Answer

[GVR_Mike]

This isn't returning the right answer. It's always returning "OLD" no
matter what the dates are.

If both Start and End date are older than a year then it should be "OLD"
If Start date is older than a year & End date is younger then its "BOTH"
If both Start & End are younger than a year then it's "NEW"

If Format(Me.TxtStart, "mm/dd/yyyy") > DateAdd("yyyy", 1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") > DateAdd("yyyy", 1, Date) Then
MsgBox "OLD"

ElseIf Format(Me.TxtStart, "mm/dd/yyyy")> DateAdd("yyyy", 1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") <= DateAdd("yyyy", 1, Date) Then
MsgBox "BOTH"

ElseIf Format(Me.TxtStart, "mm/dd/yyyy")<= DateAdd("yyyy",1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") <= DateAdd("yyyy", 1, Date) Then
MsgBox "NEW"
End If

Thanks
DS

The Format function returns a string. It appears that your string is
evaluating to be "greater than" your date so it always returns OLD.
I'd try comparing the date directly from the textbox like instead of
formatting it. See http://www.techonthenet.com/access/functions/date/format.php

 

[DS]

This isn't returning the right answer. It's always returning "OLD" no
matter what the dates are.

If both Start and End date are older than a year then it should be "OLD"
If Start date is older than a year & End date is younger then its "BOTH"
If both Start & End are younger than a year then it's "NEW"


If Format(Me.TxtStart, "mm/dd/yyyy") > DateAdd("yyyy", 1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") > DateAdd("yyyy", 1, Date) Then
MsgBox "OLD"

ElseIf Format(Me.TxtStart, "mm/dd/yyyy")> DateAdd("yyyy", 1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") <= DateAdd("yyyy", 1, Date) Then
MsgBox "BOTH"

ElseIf Format(Me.TxtStart, "mm/dd/yyyy")<= DateAdd("yyyy",1, Date) And _
Format(Me.TxtEnd, "mm/dd/yyyy") <= DateAdd("yyyy", 1, Date) Then
MsgBox "NEW"
End If

Thanks
DS

 

[DS]

Well the problem is the date is eing iserted from a calendt picket and
it in serts it as a string. How would one insert it as a date?

I'm formating it as "mm/dd/yyyy". but I guess the formating is messing
it up. Can I change the dateadd to a sting, can you compare strings?
Thanks
DS

 

[Bob Quintal]

Well the problem is the date is eing iserted from a calendt picket
and it in serts it as a string. How would one insert it as a
date?

I'm formating it as "mm/dd/yyyy". but I guess the formating is
messing it up. Can I change the dateadd to a sting, can you
compare strings? Thanks
DS

You can use Cdate() to convert a string to a date.
Assuming txtold < txtend

If cdate(me.txtstart)< dateadd("yyyy",-1,date()) then
If cdate(me.txtend)< dateadd("yyyy",-1,date()) then
msgbox "Old"
else
msgbox "Both"
end if
else
msgbox "New"
end if

 

[DS]

You can use Cdate() to convert a string to a date.
Assuming txtold < txtend

If cdate(me.txtstart)< dateadd("yyyy",-1,date()) then
If cdate(me.txtend)< dateadd("yyyy",-1,date()) then
msgbox "Old"
else
msgbox "Both"
end if
else
msgbox "New"
end if

Thanks Bob,
I used DateValue... is that the same as CDate? I realised I had one
string and one date field, I guess it was like subtracting apples and
oranges.
Thanks
DS

 

[Bob Quintal]

Thanks Bob,
I used DateValue... is that the same as CDate? I realised I had
one string and one date field, I guess it was like subtracting
apples and oranges.
Thanks
DS

cdate() and datevalue() are different, but the difference is not
applicable here, so feel free to use either.