Solved

How to block date field enter back date

Posted on 2015-01-23
10
253 Views
Last Modified: 2015-01-26
Hi Experts,
I have a date field , is any way I can block users enter back date?  for example, the datefield user only can enter 1/23/15 or 1/24/15, only the allow to enter the after today's date but not the date before today's date, and a message say something like "can not enter back date".

Thanks,
0
Comment
Question by:urjudo
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 40567389
place the codes in the beforeupdate event of the textbox

if me.text1< Date() then
   msgbox "Cannot enter back date!"
   cancel =true
   me.text1.setfocus
  exit sub
end if
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40567398
It is not impossible but is complicated by the DatePicker.
The DatePicker has no events, in and of itself.
That leaves the Change event, which will fire every time a user keystrokes in the field.
Which is not a happy thing, to say the least.

AFTER the users leave the field, then the AfterUpdate event fires.
It's easy then to compare the value entered against today's date and either fire them back into the control, or blank the control, or msgbox them about the bad choice.

But blocking them from making a bad choice in the first place--that's very hard.
0
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 100 total points
ID: 40567419
I would use the Validation Rule and Validation Text in the properties.  If you want it set all of the time, modify the field properties in table definition, if it is to be restricted on a form, modify the form control properties.
Validation Rule  Between date() and date() + 1
Validation Text  Date can be today or tomorrow.
0
 

Author Closing Comment

by:urjudo
ID: 40567427
Thank you!!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40567631
The fun starts now.
The error messages if they don't enter a valid date aren't pretty and aren't controllable.
And if they make an error today and don't notice until next week, what then?

BeforeUpdate and Validation rules exist and work -- in theory, and in practice, on the developer's machine.
In userland, they quickly become an unhappy swamp of all the little things that make an app look unpolished.
Validating with AfterUpdate gives you better control than BeforeUpdate and Validation rules -- and puts YOU in control of what the logic does.
The other two options aren't going to let the user escape the field without putting in a date that is today or tomorrow.
Even when they don't know which date it should be, or should have left it blank.
Or accidentally changed a valid value from last week and now can't leave the original data alone.
And it'll drive them crazy eventually.

Validating ALL the data on the form before it gets closed or moved to a new record is usually the most amount of work, but the best way to go about things.  That's my hard-won experience, anyway.

Nick67
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:urjudo
ID: 40570793
Hi Nick67,
Thanks for the message, the reason we put the message is because user(s) should finish yesterday's job and enter yesterday's date, but they finished today and entered yesterday's date to show that how many they finished on yesterday but which is incorrect, that's why we try to avoid this thing happen.
0
 

Author Comment

by:urjudo
ID: 40570816
Hi Rey Obrero,
I have one more question regarding the answer that you provided, I do not know if I need to open a new ticket for that.  The code is doing what I need on the date field but the only problem I notice this morning was if user(s) enter the back date and they want to exit out the form (I have a Cancel button on the form for user(s), normally user users can click Cancel to undo everything), the message still showing unless they removed the date, is anyway that when user(s) click the Cancel button on the form, the message will not show.  I did try to put :
if me.text1< Date() then
    msgbox "Cannot enter back date!"
    cancel =true
   text1 = ""
   exit sub
 end if
but I got error message.

Thanks
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40570828
I wrote this while you were posting your last result.
I do not know if I need to open a new ticket for that.
You may as well.

--------------------------------------------

You have an operational need.  Nothing wrong with that!
But both Validation and BeforeUpdate tend to have some unhappy side-consequences when you put them into production.  They both are very strict and trap a user in a control until their requirements are fulfilled, period.  And the error messages that can be generated (let's say a user enters text instead of date, by accident) don't look very friendly.

And things like
Between date() and date() + 1
Look nice -- but what happens over a weekend span?
Or a mid-week holiday?

There is much to think about, and get right.
An AfterUpdate event can be much more flexible that a Validation Rule.
And it's not as persnickety as a BeforeUpdate event, which can rub out the user's entry and leave them feeling trapped.
Even when you provide some mechanism for cancelling an entire entry, a BeforeUpdate can be squirelly
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40570836
if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
    text1 = ""
    exit sub
  end if


This is not good code.
You would want Me.Text1.Undo and not text1 = ""
This is a date.  "" is not a date and is also not Nothing or Null--it is a value and a string value-- just a string value of zero characters
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40570876
try

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
    text1 = Null  'since this is bound to a  date field
    exit sub
  end if
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

10 Experts available now in Live!

Get 1:1 Help Now