Is it posible to have a date/text field in an access db

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

Can I have a date field that is also a text field? In some
instances a date is not required and it is important that it is
specified as NA in that field.
Click to expand...

No.
You can add a checkbox beside the date to store true or false
associated with N/A, and use that to hide the textbox on a form or
report, and unhide a label that says "N/A" instead.

You can also store a date in a text field, but you lose all the
functionality of the date type.
 
Can I have a date field that is also a text field? In some instances a date
is not required and it is important that it is specified as NA in that field.
 
No.
You can add a checkbox beside the date to store true or false
associated with N/A, and use that to hide the textbox on a form or
report, and unhide a label that says "N/A" instead.

You can also store a date in a text field, but you lose all the
functionality of the date type.
Click to expand...

And you will NOT NOT NOT be able to sort by that field that has
a date in it but is defined as text.

Well, you can sort by it but it will almost certainly NOT be in the
sequence you want.

Ron
 
Actually, this is not entirely true. If you store a date in a text field in
this format YYYY/MM/DD, then the field WILL sort in correct date order.

However, this intoduces other problems like how to force your users to input
data in an unconventional date format.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Well, you can use a text field to store date values, but this poses
additional problems, see some of the other posts in this thread.

But what I'm interested in is why you think it's important that you be able
to *store* NA in the field. Would it be sufficient to leave the value NULL
(which a date field allows) and simply *display* the field value as NA in a
forum, query, or report.

For instance, in a Query, you can set the Format property to

mm/dd/yyyy;;"NA"

and if there is a date in the field it will display and if there is not, it
will display NA.

Or you could define a field in your query or report as Bdate:
nz([birthdate],"NA"), which will also display the date if there is one and
NA if there is not.

Just an alternative to think about.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
And you will NOT NOT NOT be able to sort by that field that
has a date in it but is defined as text.
Click to expand...

Absolutely. That's only one of the many facets of date functionality
that get lost if you store a date in a textt field.
Well, you can sort by it but it will almost certainly NOT be in
the sequence you want.
Click to expand...

In a query, you can get around this by sorting on cdate(datestring)
My first suggestion is far better.
Ron
Click to expand...
 
Back
Top