Newbie: help with text field mask and validation

[Andrew L.]

I need to create a text (varchar (8)) field to hold dates. I want
dates to be xx/xx/xxxx, with leading 0's for single digits. The reason
for using a text field is that I want the user to be able to input
"??" for unknown day/date/year. I could really use a hand in how to
create the format/mask/validation for this.

Thank You,
Andrew L.

 

[Pieter Wijnen]

Why not Use Null for unknown dates (which null is for?)
Or if you really *must* distinguish between not filled in & unknown, add a
Boolean Field to the table & Check box to the input form?
You can then easily substitute the date for '??' in a report, if so desired
(and on the form for that mather)

Pieter

 

[Pieter Wijnen]

Why not Use Null for unknown dates (which null is for?)
Or if you really *must* distinguish between not filled in & unknown, add a
Boolean Field to the table & Check box to the input form?
You can then easily substitute the date for '??' in a report, if so desired
(and on the form for that mather)

Pieter

I need to create a text (varchar (8)) field to hold dates. I want
dates to be xx/xx/xxxx, with leading 0's for single digits. The reason
for using a text field is that I want the user to be able to input
"??" for unknown day/date/year. I could really use a hand in how to
create the format/mask/validation for this.

Thank You,
Andrew L.

--

 

[Andrew L.]

Unknown dates could be ??/??/yyyy, mm/??/yyyy, or ??/??/??.
The dates need to be filled, it's a required field. Part of the
reasoning is that the records must be sorted by date. There is a year
(int (4)) field also, to help with this sort. I can create the table
and import data correctly, with the sort working, but I am lost when
it comes to creating the format/mask/validation for the date field.

Andrew L.

 

[John Nurick]

For a validation rule, try this
Like "[?0-9][?0-9][?0-9][?0-9][?0-9][?0-9][?0-9][?0-9]"
to require eight characters each of which can be a digit or a ?. Insert /
characters in the expression as required.

For more subtle validation, in the BeforeUpdate event of a form or control,
there's the rgxValidate() function at
http://www.mvps.org/access/modules/mdl0063.htm. With a pattern like this

[?0-3][?0-9]/[?01][?0-9]/(?:[?12][?0-9])?[?0-9]{2}

it will accept two digits or question marks for day and month, and two or
four digits and question marks for the year. If you want to reject
impossible dates, such as the 39th of August or 29th February 2005, things
get rather more complicated.