• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Date, blank and "N/A" as acceptable entries

Is it possible to use Date, blank and "N/A" as acceptable entries in a field?  

I'll need to later be able to sort by date (which is why I don't want to use text field).  help.


2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Depends on your business logic, whether you need to force a value into a column, or allow for a NULL/blank/N/A/'Hey, I dunno.'

The word Date is an Access reserved word that returns the date on your pc's system clock, and should (imho) never be used anywhere other than in VBA code when you actually want the date.
Hi jmerulla,

You cannot enter N/A in a date field.

If you use a text field you could use :

sortfield:iif(datefieldname = "N/A" , "N/A" ,format(cdate(datefieldname),"yyyy-mm-dd")

But you would have to build validation into the data entry to be sure that a valid date is being entered.

Jim P.Commented:
Unless you store the date as a text field you can't put text in it.  The way I would do it is put in a date like 1/1/1950 for N/A and then when you write your query do an

iif(MyDateField= #1/1/1950#, "N/A", MyDateField)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now