Solved

Textbox for data values in MS Access

Posted on 2016-09-09
7
35 Views
Last Modified: 2016-09-13
I have an Access form with a textbox for the user to enter a date value.  Its format is Short Date.

Under normal circumstances the user will enter a date value.  If (s)he enters anything else or enters the date in an improper format, an error message will be sent to the screen.

I'd like to keep the above behavior but for one exception.  I'm wondering if there is a way for the user to be able to select "NA" (for not applicable) or to be able to type it in.  I don't mind if I have to change the textbox to a combobox or some other object.

In other words, I want to force the user to enter a value into the box and not be able to leave it blank.  But the only values that can entered are either a date in proper format or "NA".  

I don't even care if I have to layer 2 controls on top of each other to accomplish what I'm asking. Just from the user's perspective, I'd like it to be seamless.  Either a date gets entered into the box or an "NA" gets selected (or manually entered).


Thank you
0
Comment
Question by:dbfromnewjersey
  • 4
  • 2
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
"NA" isn't a date so it CANNOT be stored in a date field.  You would have to change the data type to text and that would break everything you do that requires the field be an actual date.

The only way to keep the date as a date but still require positive entry is to use a bogus date such as 12/31/9999 and that will probably also require query and code changes.  Another option is to have a separate yes/no field that confirms the empty date.  There really isn't a good solution for your requirement.  All solutions have flaws.  You need to decide the least troublesome flaw.
0
 
LVL 18
Comment Utility
one way would be to enter 0 which you can do with:
12:00:00 AM (or more simply, 12a)
and then use the Format property to display "NA" for 0 (have filled format for null as well but that could be left off)
format code --> mm/dd/yy;mm/dd/yy;"NA";"enter date"
... you would need to use a format code when reporting so the dummy value doesn't display
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The "0" value date equates to 12/30/1899 so it is not without problems either since it will likely affect queries, etc.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18
Comment Utility
yes, it would have to be replaced with Null for calculating  ... as well as handled with format codes for reporting (queries and reports).
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The solution comes down to "what do you want to do with the "NA" date?"    Null values are less likely to force you to "handle" anything.  Both the "0" date (12/31/1899) and the "high" date (12/31/9999) will need to be handled in your queries.  That leaves the Yes/No field which will set the date to null and be there to justify it if you need to in a query.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Have a juxtaposed checkbox for the user to check for N/A.
When checked, clear the textbox with the date.

Plain a simple, and you don't violate anything nor will have to operate with "magic" dates.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Isn't that what I said?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

18 Experts available now in Live!

Get 1:1 Help Now