validation rule and formatting

[Darla123]

Hello everyone. I have a field on a form that is bound to a table that can be either "NA" "TBD" or a date. I have a validation rule on the field like this:

IsDate([Field])=True or "NA" or "TBD"

This works great, but the only problem is that I need the date to be formatted as m/d/yyyy (the don't want leading zeros) and the only place I can think of to set the format is in the table and in the text box properties, and it doesn't work, it accepts any date format. How do I work around this?

[jforbes]

The root problem you are running into is that your Date field isn't strongly typed, meaning that you are storing a Date value into a Text field. This can be done but it is a bad practice and if you have control over your data structure I would recommend putting in the effort to change the field from a Text data type to a DateTime data type.

The reasons that it is a bad practice are many and one of them includes the difficulty that you are running into right now. Other problems will show up when you go to query your data or more importantly when you try to sort or filter your records by this date column. If you store your Dates in a Text field and then sort by that column, they will be sorted alphabetically not chronologically.

So then this hypothetical situation starts to snowball like this: You will then come to the conclusion that you should store your date in yyyy/mm/dd format so that you can correctly order the rows. Then someone wont like how it is displayed and demand that the year get moved to the end. So you will then write a special routine to format it by transposing the year and day, just for reporting. Which will work at first, and then in October it starts acting up because there is now ten digits in the Date instead of nine. So you then decide to convert the text to a date before you format it display and it gets you past the problem. Then someone wants a running total of all outstanding items for the last two weeks and your head explodes because you realize the function to query for the last two weeks worth of data is a complete mess.

...I would strongly recommend to convert your column to a DateTime and an additional field to hold "TBD" and "NA". Actually, I would add a Boolean field to only track "NA" and if the Date field is Null, then consider it "TBD", because that's what a Null field most often means for a Date.

But if you are a glutton for punishment and don't want to do this, you might want to look at the Input Mask for your field. I don't know if it will work, but it might.

If that doesn't work, you might need to add an unbound field to display and edit the Date and hide the current bound field. Then in the OnCurrent event, update the unbound field with the value in the bound field and perform any display magic needed. Then on the unbound fields AfterUpdate Event, update the Bound field with the formatted value using VBA.