Date of 1/14/2010 and on not working in form

I have a form that we have been using, successfully for about two years. On
the form, I have a section with 3 text boxes. Textbox 1 (REPORTINGstart) is
a date data type with mm/dd/yyyy as the format. Textbox 2 (REPORTINGend) is a
date data type with mm/dd/yyyy as the format. Textbox 3 is a straight text
textbox titled PHASE. The way the form works, if a date is entered in the
start text box and not the end text box, the PHASE is IN PROGRESS. If there
are dates in both the start and end text boxes, the PHASE is complete. The
SQL statement in the PHASE text box is:
=IIf([tblREPORTfields]![REPORTINGstart]CDate([tblREPORTfields]![TODAY]),IIf([tblREPORTfields]![REPORTINGstart]
Is Not Null And [tblREPORTfields]![REPORTINGend] Is Null,"IN
PROGRESS",IIf([tblREPORTfields]![REPORTINGstart] Is Not Null And
[tblREPORTfields]![REPORTINGend] Is Not Null,"COMPLETE")))
There are no data validation rules in any of the text box data fields, and
there are no Before, After or other events. This particular string of events
has worked successfully up until we entered the date 1/14/2010 or later
(1/15/2010, 1/16/2010, etc.) For dates 1/13/2010 and earlier, it works fine.
When entering 1/14/2010 and later the PHASE text box does not populate. Any
ideas how to correct this? Is there an issue with dates after 1/14/2010?

You need to ensure that the column you are comparing to is defined as a
date/time column.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Gnerks" wrote:

I have a form that we have been using, successfully for about two years. On
the form, I have a section with 3 text boxes. Textbox 1 (REPORTINGstart) is
a date data type with mm/dd/yyyy as the format. Textbox 2 (REPORTINGend) is a
date data type with mm/dd/yyyy as the format. Textbox 3 is a straight text
textbox titled PHASE. The way the form works, if a date is entered in the
start text box and not the end text box, the PHASE is IN PROGRESS. If there
are dates in both the start and end text boxes, the PHASE is complete. The
SQL statement in the PHASE text box is:
=IIf([tblREPORTfields]![REPORTINGstart]CDate([tblREPORTfields]![TODAY]),IIf([tblREPORTfields]![REPORTINGstart]
Is Not Null And [tblREPORTfields]![REPORTINGend] Is Null,"IN
PROGRESS",IIf([tblREPORTfields]![REPORTINGstart] Is Not Null And
[tblREPORTfields]![REPORTINGend] Is Not Null,"COMPLETE")))
There are no data validation rules in any of the text box data fields, and
there are no Before, After or other events. This particular string of events
has worked successfully up until we entered the date 1/14/2010 or later
(1/15/2010, 1/16/2010, etc.) For dates 1/13/2010 and earlier, it works fine.
When entering 1/14/2010 and later the PHASE text box does not populate. Any
ideas how to correct this? Is there an issue with dates after 1/14/2010?

What updates [tblREPORTfields]![TODAY] and why must it be converted to a date?

--
Build a little, test a little.


"Gnerks" wrote:

I have a form that we have been using, successfully for about two years. On
the form, I have a section with 3 text boxes. Textbox 1 (REPORTINGstart) is
a date data type with mm/dd/yyyy as the format. Textbox 2 (REPORTINGend) is a
date data type with mm/dd/yyyy as the format. Textbox 3 is a straight text
textbox titled PHASE. The way the form works, if a date is entered in the
start text box and not the end text box, the PHASE is IN PROGRESS. If there
are dates in both the start and end text boxes, the PHASE is complete. The
SQL statement in the PHASE text box is:
=IIf([tblREPORTfields]![REPORTINGstart]CDate([tblREPORTfields]![TODAY]),IIf([tblREPORTfields]![REPORTINGstart]
Is Not Null And [tblREPORTfields]![REPORTINGend] Is Null,"IN
PROGRESS",IIf([tblREPORTfields]![REPORTINGstart] Is Not Null And
[tblREPORTfields]![REPORTINGend] Is Not Null,"COMPLETE")))
There are no data validation rules in any of the text box data fields, and
there are no Before, After or other events. This particular string of events
has worked successfully up until we entered the date 1/14/2010 or later
(1/15/2010, 1/16/2010, etc.) For dates 1/13/2010 and earlier, it works fine.
When entering 1/14/2010 and later the PHASE text box does not populate. Any
ideas how to correct this? Is there an issue with dates after 1/14/2010?