?
Solved

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

Posted on 2006-07-24
3
Medium Priority
?
352 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 66

Accepted Solution

by:
Jim Horn earned 600 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 400 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

770 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