?
Solved

Textbox for data values in MS Access

Posted on 2016-09-09
7
Medium Priority
?
91 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 41

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 24
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 41

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 24
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 41

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 53

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 41

Expert Comment

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month3 days, 13 hours left to enroll

601 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