SOLVED

How to handle indefinite date

%3CLINGO-SUB%20id%3D%22lingo-sub-2458013%22%20slang%3D%22en-US%22%3EHow%20to%20handle%20indefinite%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2458013%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Microsoft%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20date%20field%20and%20one%20of%20the%20possible%20values%20is%20%22Life%22%20as%20in%20%22for%20life%22.%26nbsp%3B%20Is%20there%20a%20way%20to%20convert%20the%20text%20%22Life%22%20into%20a%20date%20value%20that%20is%20indefinite%3F%26nbsp%3B%20Or%20should%20I%20just%20convert%20all%20the%20date%20values%20into%20short%20text%2C%20use%20an%20input%20mask%20and%20store%20it%20that%20way%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20very%20new%20to%20access%20so%20as%20always%20thank%20you%20for%20your%20input.%26nbsp%3B%20I%20look%20forward%20to%20working%20with%20you%20in%20solving%20this%20problem.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3EEb%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2458013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDate%20Field%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2458034%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20handle%20indefinite%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2458034%22%20slang%3D%22en-US%22%3EDate%20fields%20must%20either%20be%20valid%20dates%20or%20null%3CBR%20%2F%3EI%20wouldn't%20convert%20all%20dates%20to%20text%20values%20as%20that%20will%20cause%20other%20issues%20e.g.%20sorting%20by%20date%20field%20in%20queries.%3CBR%20%2F%3EWould%20using%20something%20like%20%231%2F1%2F9999%23%20be%20an%20acceptable%20alternative%20for%20'Life'%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2458037%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20handle%20indefinite%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2458037%22%20slang%3D%22en-US%22%3EKnew%20I%20was%20missing%20something%20obvious.%20Thank%20you!%3C%2FLINGO-BODY%3E
Occasional Contributor

Hey Microsoft Community,

 

I have a date field and one of the possible values is "Life" as in "for life".  Is there a way to convert the text "Life" into a date value that is indefinite?  Or should I just convert all the date values into short text, use an input mask and store it that way?  

 

I am very new to access so as always thank you for your input.  I look forward to working with you in solving this problem.  

 

Sincerely,

Eb

3 Replies
best response confirmed by ebenton (Occasional Contributor)
Solution
Date fields must either be valid dates or null
I wouldn't convert all dates to text values as that will cause other issues e.g. sorting by date field in queries.
Would using something like #1/1/9999# be an acceptable alternative for 'Life'?
Knew I was missing something obvious. Thank you!

@ebenton 

You're welcome