Short Date Field and "N/A"

[sjl]

I have a short date field that I want to change to also allow “N/A”.

The current input mask is: 99/99/0000;0;_
The current validation rule is: Between #1/1/2006# And #1/1/2015#

Is it possible to add a “N/A” to the field???
(I've not been able to get it to work)

 

[boblarson]

You'll have to use an unbound text box for this because a bound date field cannot accept text.

 

[sjl]

Thanks for heading me in the right direction, Bob.

I'll look into unbound text boxes for my Form. However, would I also need to add "Or "N/A" to the Validation Text of the short date field in the Table? what about additions to the input mask? thanks much.

 

[boblarson]

Thanks for heading me in the right direction, Bob.

I'll look into unbound text boxes for my Form. However, would I also need to add "Or "N/A" to the Validation Text of the short date field in the Table? what about additions to the input mask? thanks much.

You wouldn't validate at table level. Validate on the form. And, you can have a bound text box (hidden) that will store your date and your displayed text box can display something based on that text box.

 

[sjl]

How interesting. I'll give it a go.
Gracias!
S.

 

[Pat Hartman]

You'll need two fields in the table. One to hold valid dates and the other to hold "N/A". Code in the form's current event would evaluate the two table fields and create a value for the form's unbound field and code in the form's BeforeUpdate event would determine which value to save.

 

[RoyVidar]

Do you have to store that text? Or is it just for display purposes?

If it's only for display purposes, you could enter something like this into the format property of the control where you need it displayed or into the format property of the table field

mm\/dd\/yyyy;mm\/dd\/yyyy;mm\/dd\/yyyy;"N/A"

Then every Null entry (where no date is entered), will display as N/A. No extra field, control or other workaround should be needed.

You'd need to alter the validation rule to

Between #1/1/2006# And #1/1/2015# Or Is Null

 

[Pat Hartman]

The meaning of null is generally "unknown". The meaning of "N/A" would be known. If null and "N/A" can be considered to have the same meaning, then storing "N/A" would not be required and we can go back to simply having an unbound field that displays N/A or a valid date. You still can't use a bound field to do this since "N/A" is a string and therefore not a valid date value.