Solved

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

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now