Text to Date Conversion

[Gnerks]

I am building an ACCESS form (Access 2003) that I imported an Excel table to
build from. The form has a set of option buttons, that if selected, enter
N/A into 4 text boxes. However, if the option buttons are not selected, the
text boxes contain dates. The dates are selected from the Access Active
Control calendar. Because the fields are either N/A (text) or a date, the
Field Data Type on my table is text. If I change the Field Data Type from
text to date - the N/A's disappear.

Is there a Field Data Type that will accept both formats in one? If not,
how do i convert the text date into an actual date that will be manipulated
via other controls on the form?

For instance, if textbox #1 is N/A then the remaining textboxes on that row
on my form are N/A.

However, if textbox #1 is a date - say 12/15/07, then I need to be able to
compare that date with a project start date (in its own textbox), and if it
is less than the project start date then a 4th textbox in the row has text of
"In-Progress", otherwise the 4th textbox in the row has text of "Delayed".
The (4th) status textbox value is dependent on the comparison of the two
dates.

The comparison is working to an extent - if gives me the correct 4th textbox
(status) of In-Progress or Delayed by simply entering the following Control
Source in the status textbox :
=IIf([txtSTART1]>[txtCalculation1],"Delayed","In-Progress"). However, if the
two dates occur beyond the current year, the the wrong status appears, but
not every time. Example: If I have a project date of 12/26/07 and a start
date (1st textbox) of 12/28/07 - then the status (4th textbox) should be
"Delayed" because my start date is after my project date and vice versua, if
my start date is 12/25/07 the status is "In-progress". As I said, this works
as long as I don't go into the new year. With a project date of 12/26/07 and
a start date of 1/8/08 the status becomes "In-Progress" because 1/8 is less
than 12/26 - it appears as if the entire date - including the year - is not
being evaluated.

Any ideas how to force the entire date to be converted from text to a date,
and then the entire date be recognized in my Control Source?

 

[Douglas J. Steele]

Try

=IIf([txtSTART1] <> "N/A" And [txtCalculation1] <> "N/A",
IIf(CDate([txtSTART1])>CDate([txtCalculation1]),"Delayed","In-Progress"),
Null)

 

[Gnerks]

This works great - thank you!

Try

=IIf([txtSTART1] <> "N/A" And [txtCalculation1] <> "N/A",
IIf(CDate([txtSTART1])>CDate([txtCalculation1]),"Delayed","In-Progress"),
Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am building an ACCESS form (Access 2003) that I imported an Excel table
to
build from. The form has a set of option buttons, that if selected, enter
N/A into 4 text boxes. However, if the option buttons are not selected,
the
text boxes contain dates. The dates are selected from the Access Active
Control calendar. Because the fields are either N/A (text) or a date, the
Field Data Type on my table is text. If I change the Field Data Type from
text to date - the N/A's disappear.

Is there a Field Data Type that will accept both formats in one? If not,
how do i convert the text date into an actual date that will be
manipulated
via other controls on the form?

For instance, if textbox #1 is N/A then the remaining textboxes on that
row
on my form are N/A.

However, if textbox #1 is a date - say 12/15/07, then I need to be able to
compare that date with a project start date (in its own textbox), and if
it
is less than the project start date then a 4th textbox in the row has text
of
"In-Progress", otherwise the 4th textbox in the row has text of "Delayed".
The (4th) status textbox value is dependent on the comparison of the two
dates.

The comparison is working to an extent - if gives me the correct 4th
textbox
(status) of In-Progress or Delayed by simply entering the following
Control
Source in the status textbox :
=IIf([txtSTART1]>[txtCalculation1],"Delayed","In-Progress"). However, if
the
two dates occur beyond the current year, the the wrong status appears, but
not every time. Example: If I have a project date of 12/26/07 and a
start
date (1st textbox) of 12/28/07 - then the status (4th textbox) should be
"Delayed" because my start date is after my project date and vice versua,
if
my start date is 12/25/07 the status is "In-progress". As I said, this
works
as long as I don't go into the new year. With a project date of 12/26/07
and
a start date of 1/8/08 the status becomes "In-Progress" because 1/8 is
less
than 12/26 - it appears as if the entire date - including the year - is
not
being evaluated.

Any ideas how to force the entire date to be converted from text to a
date,
and then the entire date be recognized in my Control Source?