"N/A" Date Value

[Ian Smith]

I have a date field. It may be null because it hasn't been
entered. It may have a real date value. Or, it may be non-
applicable, which must be differentiated from null.

What I am trying to do is to use "01/01/100" (earliest
possible date) as my "n/a" value, and to display "n/a" if
that is the value in the field.

I have a visible field on the form that contains iif
([DateField] = #01/01/100#,"n/a",[DateField]). It isn't
updateable as it contains an expression. In front of that,
I have an invisible text field.

When the date field is entered, I make the text field
visible, take the input, then either set the date field to
01/01/100 or to the actual date entered.

Now how do I make the field invisible again? If I move the
focus to the date field, I will trigger the Entered Event
and repeat the cycle. If I don't move the focus, I can't
make the field invisible.

Does anyone have a better way>

 

[Allen Browne]

Ian, it may be easier for the data entry people if you gave them a check box
to indicate Not Applicable.

You can display the result on a report as:
=IIf(IsDate([MyDate]), Format([MyDate], "mm/dd/yyyy"),
IIf([DateNA], "N/A", Null))

That assumes a Date/Time field named "MyDate", and a Yes/No field named
"DateNA".

 

[Wayne Morgan]

Try reversing which textbox is invisible. Bind the back textbox to the field in the
query/table and set its visible property to No. Leave the front textbox that the user
types in visible. In the AfterUpdate event of the front textbox, set the value of the back
textbox accordingly. In the Form's OnCurrent event you would read the value in the back
textbox and set the value of the front textbox. Since the code is being done in the module
instead of the Control Source, the textboxes will still be updateable.