?
Solved

Textbox for data values in MS Access

Posted on 2016-09-09
7
Medium Priority
?
72 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
[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
  • 4
  • 2
7 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 41791952
"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 22
ID: 41792061
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 38

Expert Comment

by:PatHartman
ID: 41792076
The "0" value date equates to 12/30/1899 so it is not without problems either since it will likely affect queries, etc.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 22
ID: 41792082
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 38

Expert Comment

by:PatHartman
ID: 41792088
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 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41792294
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 38

Expert Comment

by:PatHartman
ID: 41796635
Isn't that what I said?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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