Solved

How to block date field enter back date

Posted on 2015-01-23
10
263 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 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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