Solved

Textbox for data values in MS Access

Posted on 2016-09-09
7
63 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 37

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 21
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 37

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21
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 37

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 50

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 37

Expert Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

690 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