Access-comparing Dates

[espinosaja]

I have a table with dates of surveys; the next column adds 5 years to that date. the 3rd column compares the two dates. if the second column is less than todays date it will give a message if not it will insert the date from the 2nd column

ex.
A:survey date
B(Expr1):survey date + 5 years
C: if B < 2/6/2010 then "Turn in new Survey" if B>"2/6/2010" then "B"

Survey Message: IIf(([Expr1])<"2/6/2010","PAST DUE, PLEASE SUMBIT A NEW SURVEY WITH PICTURES BY Feb. 6, 2012",[Expr1])

value of Ex.

A:3/5/2001


B:3/5/2006
C:Turn in new Survey
Great! this works fine

But:

A:12/12/2008
B: 12/12/2013
C:s/b 2/12/2013 *

*but it compares the 1 (12-December) with 2 and it thinks it's smaller and it puts the message! how do I compare values or the whole date?

[June7]

Presume you mean you have a query doing these calcs, not a table.
The criteria date is shown as hard code. Should that be the current date?

Syntax for B and C wrong.
Without DateAdd function, adding a number to a date defaults to day unit. Don't see how B could be working.
B: DateAdd("yyyy",5,[survey date])
C: IIf([B] < Date(), "Turn in new Survey", [B])

The revised due date is also hard code. Should that be calculated?

[espinosaja]

Sorry here are the actual formulas:

B: Expr1: DateAdd("yyyy",5,[survey])

So are you saying in C: instead of "2/6/2010" I should put Date(2/6/2010)?

[espinosaja]

I'm trying to post the table so you can see it!

[June7]

No, Date() is a function with no arguments. It will return the current date. Now() will return current date and time. Test them in the VBA editor immediate window with: ?Date()

[espinosaja]

Still having same date comparison problem.

if a date in Col A is 2/6/2010 and in Col B is 12/6/2014

I am comparing to see if the date in Col B is less than the date in col A. if so, then a message should pop up, and if not then it should return with the date in col. B.

well, when it returns it compares the digits from left to right so it considers 1 (of 12) less than 2!

here's my expression:
Survey Message: IIf(([Expr1])<"2/6/2010","PAST DUE, PLEASE SUMBIT A NEW SURVEY WITH PICTURES BY Feb. 6, 2012",[Expr1])

HELP!!

Thank you

[RayMilhon]

If [EXPR1] is a date then change "2/6/2010" to #2/6/2010#

[June7]

Good catch Ray.

Use apostrophe delimiters for text and # for dates, numbers need none.