Solved

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

Posted on 2006-07-24
3
348 Views
Last Modified: 2012-08-14
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.

thanks

0
Comment
Question by:jmerulla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 150 total points
ID: 17170129
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.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 17170135
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.

Pete
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 17171175
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)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question