Handling nonspecific dates

[Western_Neil]

Hello

I have two questions about dates, Is there a way of setting up the tables, fields and relationships so that a nonspecific date can be used with dates?

I have historical data the can gives dates as early July, near, by, on, later etc. I can handle this by guessing but that’s changing the data which should be NO. Adding a field to indicated that its nonspecific doesn’t help the ordering of the dates. All ideas are welcomed.

The second one I will post on a new thread.



Thanks Neil

[nrgins]

Hello, Western Neil! Southwestern Neil here. :-)

To answer your question, no, you can't use a date field for that. You'd have to use a text field.

However, you can create a SECOND field as a date field which has your guess as to what the date is, and use that for sorting. That way, you don't change the actual data, and you still have a date you can sort by.

[Western_Neil]

Thanks Neil

I like it. If I also add an accuracy field that should handle it for what I need. An example of what I'm thinking would be
2016/7/15 (date) .. 15 (+- days) ... Mid July 2016 (The real vaule). With a firm date being
2016/7/4 (date) .... 0 (+- days) ... 4 Jul 2016.

The only hang up is entering the extra data, but with smart forms that should handle it.

Neil

[nrgins]

Yeah, two things you can do in your form. Let's say your fields are called DateValue, Accuracy, and RealValue. In your DateValue control's After Update event, you can put:

Me.Accuracy = 0
Me.RealValue = Me.DateValue

This way, if you have an actual date value, the Accuracy and RealValue values would be automatically completed for you when you complete the DateValue field.

The problem with that approach is that then you'd be going back and forth between entering actual dates in the DateValue field and non-actual dates in the RealValue field. Not only would that be annoying, it might also lead to data errors (although DateValue being a Date field won't allow non-date values, so that would help).

Another approach would be to always use the RealValue field for data entry, and have the form decide if it's an actual date or not. Thus, in the RealValue control's After Update event, you'd do:

If IsDate(Me.RealValue) Then
    Me.DateValue = Me.RealValue
    Me.Accuracy = 0
Else
    Me.DateValue.SetFocus
End If

That places the cursor in the DateValue field if the RealValue field doesn't contain a date value.

[Western_Neil]

Thanks
Both are good ideas, and I will probably use the second one
Thanks Neil

[nrgins]

You're welcome, Neil.

(Neils FTW!)