?
Solved

How to block date field enter back date

Posted on 2015-01-23
10
Medium Priority
?
274 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 1600 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 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