Solved

How to block date field enter back date

Posted on 2015-01-23
10
269 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
[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
  • 3
  • 2
  • +1
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 19

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 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