2 Fields or 1 Field

[Tom]

All:

I have a design question that I can't quite decide on the best course
of action and was looking for some guidance...

User wants a field that progresses from:
* "N/A" when the record is first created; to
* "Required" when another field value is changed from one value to
another; to
* a date when an action is complete

Am I better off creating two fields and storing the date separate from
the text, or am I better off using just a text field and storing the
date as text. I like the simplicity of one field - only one thing to
worry about on the form and report, but I'm nervous about storing a
date in a text field.

Some additional information that might be helpful:
* I won't need to do any date math on the date in question
* very limited number of users (less than 5, all in one place, all
fairly sophisticated) so training them on what is expected is not a
problem
* BE is SQLSERVER

Any thoughts?

Thanks

Tom

 

[Golfinray]

In my opinion, dates really need to be in date datatype fields and text in
text. Even though you may not have to query it is still generally a good
idea. And if you were to have to query at some future time, it will be a
little more difficult.

 

[Ken Sheridan]

Tom:

I'd agree with the other respondent. It seems to me that its not a question
of convenience, but a more fundamental one, in that a date and the 'N/A' or
'Required' values are values of two different attribute types. A column in a
table should only represent one attribute type, and each row in the table
should have only legal values of that attribute type at that column position.

To return only one value in a report for instance simply requires a computed
control, e.g. if the column names are Status and DateCompleted:

=IIf(IsNull([DateCompleted], [Status], [DateCompleted])

You'd need to build in appropriate validation to ensure that the values in
Status and DateCompleted do not conflict in any one row of course. Don't
rely on the sophistication of the users to prevent inconsistent data.

'N/A' can be the DefaultValue property of Status of course.

Ken Sheridan
Stafford, England

 

[Tom]

Thanks - I have strong inclinations to make two fields also, for all
the reasons you both cited.

My concern though was what happens when we get to the last stage of
the process and all that is needed is a date? Extra validation needs
to occur so I don't get inconsistent values, extra work needs to be
done to get right value to display on reports, and extra thought needs
to go into form design so users aren't left with a null field and
wondering why. All fairly trivial to deal with, but cumulatively, I
wasn't so sure.

Bascially, that nagging voice in the back of my head kept saying, what
is so different about storing a date in a text field when you don't
intend on doing date math as opposed to storing a telephone number in
a text field. After all, the telephone number is a "number", but
storing it as such is a no-no...

Thanks

Tom

 

[BruceM]

A telephone number can be text. Commercial phone numbers are often
contrived so that they spell something. The numbers or letters are
characters to identify the button to push on the phone (or the location on
the dial, in another era). A date, on the other hand, is a number to which
another number can be added or subtracted. For instance, if the due date is
30 days from the start date, you can perform that calculation with a date
field. Access is pretty good at reading a "date-like" text entry as a date,
but if there is a typo Access could either return the wrong result or fail
to return any result. You can also use type conversion functions.
CDate([SomeField]) will attempt to render SomeField as a date. It may or
may not succeed, depending on what it finds in SomeField. However, this
involves another layer of complexity. Granted, it is a little more complex
than simply binding a text box to a field, but not much. Ken showed how you
can do that with a report, where you don't interact with the data.

One approach on a form is to use two text boxes (I will call them txtOne and
txtTwo) of the same size and style, and in the same position on the form.
In the form's Current event you could have:

If Me.NewRecord Then
With Me.txtOne
.Visible = True
.Value = "N/A"
End With
Else
If Me.SomeField = "Some Value" Then
With Me.txtOne
.Visible = True
.Value = "Required"
End With
Else
Me.txtOne.Visible = False
Me.txtTwo.Visible = True
End If
End If

You would also need code in the After Update event of the Control bound to
SomeField to change which text box is visible. Note that since the values
"N/A" or "Required" are dependent on other fields, there is probably no need
to store that value. The date is all you need to store.

You don't describe what indicates an action is complete. Is a field filled
in? With a few more details it might have been possible to come up with a
tidier scenario. In one way it is more work to do it this way, but in
another way it is more work if you ever need to filter, etc. using a date
that is stored as text.

 

[Tom]

Note that since the values "N/A" or "Required" are dependent on other fields, there is probably no need to store that value.  The date is all you need to store.

Ha! - that's the ticket - the "N/A" and "Required" as actually
"calculated" values that don't need to be stored. And in fact, trying
to store them is mucking up the design (as it frequently does).

All I really need to store is the date once the final action is
complete. Its really nothing more than a glorified yes/no (there is
either a date or not) at this point, but its not inconceivable that
design requirements will change and I will need to do date math.

Thank you all for helping me clarify the design - one (date) field it
is with calculated values in an unbound field for N/A and Required. A
little bit of VBA to display the right text box and I'm good to go!

Tom

 

[Ken Sheridan]

Tom:

A telephone number is not a 'number' because its an encoding system. Zip
codes and ISBN numbers are other examples. Its nothing to do with the fact
that arithmetical operations are not required to be performed on them; that's
an argument which is often put forward, but it’s a specious one. A column
can perfectly validly be a 'number' without any arithmetic being involved
because the number has ordinal or cardinal significance in terms of the
attribute type of which the number is a value.

We should not get obsessed about which data type is the 'correct' one for
any attribute type, however. So long as the principles of the model are
respected it doesn't actually matter. Using a date/time data type for
date/time values is a convenience, but if the date is stored as text then its
perfectly possible to process the values in any way you wish. If it is done
then it’s a good idea to use an internationally unambiguous format such as
the ISO standard YYYY-MM-DD hh:mm:ss format (Access uses nn for the minutes
of course rather than using the upper and lower case to distinguish between
months and minutes as the ISO standard does).

In your case the question is not one of data types at all, but solely
whether the model requires one or two columns and the criterion here is, I
believe, as I said in my first reply, that to use a single column in the way
you describe would be to have values of two different attribute types in one
column; that's just playing fast and loose with the basic principles of the
database relational model.

As far as the need for 'extra validation' is concerned, yes it does require
a little more work, but that's what we are paid for. No pain, no gain, as
they say.

Ken Sheridan
Stafford, England

 

[BruceM]

Glad to hear you clarified the design requirements in your mind. One note
is that there is an unbound control, not an unbound field. A field is in a
table or query, and cannot by itself be bound or unbound. Note that you may
be able to perform the calculation in a query field, and bind a control to
that field or use its value in other ways such as to determine whether
another control is visible or not.

Note that since the values "N/A" or "Required" are dependent on other
fields, there is probably no need to store that value. The date is all you
need to store.

Ha! - that's the ticket - the "N/A" and "Required" as actually
"calculated" values that don't need to be stored. And in fact, trying
to store them is mucking up the design (as it frequently does).

All I really need to store is the date once the final action is
complete. Its really nothing more than a glorified yes/no (there is
either a date or not) at this point, but its not inconceivable that
design requirements will change and I will need to do date math.

Thank you all for helping me clarify the design - one (date) field it
is with calculated values in an unbound field for N/A and Required. A
little bit of VBA to display the right text box and I'm good to go!

Tom

 

[Tom]

One note is that there is an unbound control, not an unbound field.  

Poor proof read on my part - that's what I meant.

Note that you may
be able to perform the calculation in a query field, and bind a control to
that field or use its value in other ways such as to determine whether
another control is visible or not.

That's what I ultimately did and it seems to be working fine for me.

Thanks again

 

[BruceM]

I've done the same sort of thing in newsgroup postings. Sounds like
everything is on track.

One note is that there is an unbound control, not an unbound field.

Poor proof read on my part - that's what I meant.

Note that you may
be able to perform the calculation in a query field, and bind a control to
that field or use its value in other ways such as to determine whether
another control is visible or not.

That's what I ultimately did and it seems to be working fine for me.

Thanks again