Not Returning Correct Answer

  • Thread starter Thread starter GVR_Mike
  • Start date Start date
G

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
Click to expand...

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
 
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
 
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
 
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
Click to expand...
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
 
Bob said:
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
Click to expand...
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
 
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
Click to expand...
cdate() and datevalue() are different, but the difference is not
applicable here, so feel free to use either.
 
Back
Top