Solved

Textbox for data values in MS Access

Posted on 2016-09-09
7
53 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 36

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 19
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 36

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 19
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 36

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 49

Accepted Solution

by:
Gustav Brock earned 500 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 36

Expert Comment

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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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.
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 …
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…

830 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