Solved

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

Posted on 2006-07-24
3
344 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

679 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