Always future dates

I have a form with an unbound text box for entering a date. The text
box's format property is set to Short Date. Here's the problem, based
on today (09/14/07):
If a user enters 01/15 into the text box, Access will expand that date
to 01/15/07. The users would like the date to be expanded to 01/15/08,
the future date instead of one in the past.

Can someone suggest the easiest way to force the date into the future?
Is there some sort of formatting property I can set, or would this
need to be done in code? If it's in code, have you seen a sample
anywhere that would give me a good starting point?

Thanks!

0
Scott
9/14/2007 1:14:15 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
719 Views

Similar Articles

[PageSpeed] 33

Use the AfterUpdate event procedure of the text box to add one year if the 
date is already past.

This kind of thing (substituting your text box name for Text0):

    Private Sub Text0_AfterUpate
        If Me.Text0 < Date() Then
            Me.Text0 = DateAdd("yyyy", 1, Me.Text0)
        End If
    End Sub

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Scott" <millardgroups@gmail.com> wrote in message
news:1189775655.687164.40800@g4g2000hsf.googlegroups.com...
>I have a form with an unbound text box for entering a date. The text
> box's format property is set to Short Date. Here's the problem, based
> on today (09/14/07):
> If a user enters 01/15 into the text box, Access will expand that date
> to 01/15/07. The users would like the date to be expanded to 01/15/08,
> the future date instead of one in the past.
>
> Can someone suggest the easiest way to force the date into the future?
> Is there some sort of formatting property I can set, or would this
> need to be done in code? If it's in code, have you seen a sample
> anywhere that would give me a good starting point?
>
> Thanks! 

0
Allen
9/14/2007 2:19:09 PM
Thanks for the response, Allen. Your comments and your site have
always helped me out and are a real value to the community.

Your suggestion is on target, but I was worried about about one
problem I didn't list: sometimes the users will legitimately need to
enter a past date. As a result, I need to only move the date when a
user enters a short date like:
m/d, mm/d, mm/dd, or m/dd (or the same, except with a space instead of
a slash). These are the cases that Access expands out to a full date
in the current year.

To solve this, I've taken an example from your site (which is great!)
and customized it. I've modified the items from http://allenbrowne.com/ser-34.html
into this subroutine that moves dates. Hopefully it helps someone in
the future. Allen, feel free to comment if you see something that
could be improved... especially if you know of a way to catch the
value of a bound field before a mouse click (see below).

Thanks again,

Scott
------------------------------------------------------------
Sub subFutureDates(ctl As Control, Optional KeyAscii As Integer)

    ' Purpose:  If a user enters a date without a year, always set
that to a future date.
    '               These dates can be: m/d, mm/d, mm/dd, or m/dd (or
the same, except with a space instead of a slash).
    ' Usage:    Call the subroutine from the control's KeyDown event
procedure:
    '                     Call subFutureDates(Me.MyTextBox, KeyAscii)
    '               And call the subroutine from the control's KeyDown
event procedure, omitting the optional KeyAscii
    '                     Call subFutureDates(Me.MyTextBox)
    '
    '               NOTE: You must put this in the KeyDown event, NOT
KeyPress
    '               KeyPress will not capture return or tab keys
    '                    (See MSDN article titled, "Order of events
for keystrokes and mouse clicks")
    '
    ' NOTE:     If you have a control bound to a date field, you can
catch users exiting the field using a
    '           tab or enter keystroke, but you WILL NOT be able to
catch a mouse click before the date changes.
    '           Access modifies the date as soon as a user clicks out
of the field, before any events fire
    '           So, by the time you can grab the date you won't be
able to tell what was entered.
    '           One way to work around this: make your field unbound,
then copy the value after you clean it up.
    '
    ' Error handling: add your own as desired!

    Dim dteDateValue As Date

    'KeyAscii is optional in order to allow us to call this from
    'a control's exit event

    'We only act if the user hits tab or enter or if KeyAscii is null
    If (KeyAscii = Asc(vbTab)) Or (KeyAscii = 13) Or (KeyAscii = 0)
Then

        'To avoid an error, we'll set the variable to today
        dteDateValue = date

        'First, check if the entry is either large enough to be a full
date
        'or is too small to be a valid date
        'If it is either, then then we'll leave it and exit
        'The smallest possible full date is like 1/1/1, so five
characters
        'The smallest possible month/date combo is three characters,
like 1/1

        If (Len(ctl.Text) >= 5) Or (Len(ctl.Text) < 3) Then Exit Sub

        'OK.. we have a partial date
        'Check if the date would be before today
        'To keep this simple, I'm NOT validating that this value can
be a date
        'Instead, we'll just resume next...
        On Error Resume Next
        dteDateValue = CDate(ctl.Text)
        On Error GoTo 0

        'Now, if the date is less than today, then we'll move it...
        If dteDateValue < date Then

            'The date is before today, so let's move it to next year
            dteDateValue = DateAdd("yyyy", 1, CDate(ctl.Text))
            ctl.Text = dteDateValue

        End If

    End If

   Exit Sub


0
Scott
9/17/2007 8:33:41 PM
Fair enough. It's always good to see someone taking the suggestions further 
and creating their own solutions. Thanks for posting so others can benefit.

If you need to know whether the user entered just something such as 3/5 
rather than including the year, you can examine the Text property of the 
control in its AfterUpdate event procedure. While the Value will always 
return a full date, the Text will show you what's actually visible there at 
the time. This lets you take the action you need.

This code for dividing a value by 100 if the user did not actually type the 
percent sign takes a similiar approach:
    http://allenbrowne.com/casu-16.html

All the best

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Scott" <millardgroups@gmail.com> wrote in message
news:1190061221.376464.290210@19g2000hsx.googlegroups.com...
> Thanks for the response, Allen. Your comments and your site have
> always helped me out and are a real value to the community.
>
> Your suggestion is on target, but I was worried about about one
> problem I didn't list: sometimes the users will legitimately need to
> enter a past date. As a result, I need to only move the date when a
> user enters a short date like:
> m/d, mm/d, mm/dd, or m/dd (or the same, except with a space instead of
> a slash). These are the cases that Access expands out to a full date
> in the current year.
>
> To solve this, I've taken an example from your site (which is great!)
> and customized it. I've modified the items from 
> http://allenbrowne.com/ser-34.html
> into this subroutine that moves dates. Hopefully it helps someone in
> the future. Allen, feel free to comment if you see something that
> could be improved... especially if you know of a way to catch the
> value of a bound field before a mouse click (see below).
>
> Thanks again,
>
> Scott
> ------------------------------------------------------------
> Sub subFutureDates(ctl As Control, Optional KeyAscii As Integer)
>
>    ' Purpose:  If a user enters a date without a year, always set
> that to a future date.
>    '               These dates can be: m/d, mm/d, mm/dd, or m/dd (or
> the same, except with a space instead of a slash).
>    ' Usage:    Call the subroutine from the control's KeyDown event
> procedure:
>    '                     Call subFutureDates(Me.MyTextBox, KeyAscii)
>    '               And call the subroutine from the control's KeyDown
> event procedure, omitting the optional KeyAscii
>    '                     Call subFutureDates(Me.MyTextBox)
>    '
>    '               NOTE: You must put this in the KeyDown event, NOT
> KeyPress
>    '               KeyPress will not capture return or tab keys
>    '                    (See MSDN article titled, "Order of events
> for keystrokes and mouse clicks")
>    '
>    ' NOTE:     If you have a control bound to a date field, you can
> catch users exiting the field using a
>    '           tab or enter keystroke, but you WILL NOT be able to
> catch a mouse click before the date changes.
>    '           Access modifies the date as soon as a user clicks out
> of the field, before any events fire
>    '           So, by the time you can grab the date you won't be
> able to tell what was entered.
>    '           One way to work around this: make your field unbound,
> then copy the value after you clean it up.
>    '
>    ' Error handling: add your own as desired!
>
>    Dim dteDateValue As Date
>
>    'KeyAscii is optional in order to allow us to call this from
>    'a control's exit event
>
>    'We only act if the user hits tab or enter or if KeyAscii is null
>    If (KeyAscii = Asc(vbTab)) Or (KeyAscii = 13) Or (KeyAscii = 0)
> Then
>
>        'To avoid an error, we'll set the variable to today
>        dteDateValue = date
>
>        'First, check if the entry is either large enough to be a full
> date
>        'or is too small to be a valid date
>        'If it is either, then then we'll leave it and exit
>        'The smallest possible full date is like 1/1/1, so five
> characters
>        'The smallest possible month/date combo is three characters,
> like 1/1
>
>        If (Len(ctl.Text) >= 5) Or (Len(ctl.Text) < 3) Then Exit Sub
>
>        'OK.. we have a partial date
>        'Check if the date would be before today
>        'To keep this simple, I'm NOT validating that this value can
> be a date
>        'Instead, we'll just resume next...
>        On Error Resume Next
>        dteDateValue = CDate(ctl.Text)
>        On Error GoTo 0
>
>        'Now, if the date is less than today, then we'll move it...
>        If dteDateValue < date Then
>
>            'The date is before today, so let's move it to next year
>            dteDateValue = DateAdd("yyyy", 1, CDate(ctl.Text))
>            ctl.Text = dteDateValue
>
>        End If
>
>    End If
>
>   Exit Sub 

0
Allen
9/18/2007 9:42:08 AM
Reply:

Similar Artilces:

OWA Date format problem on SBS 2003
First of all I have everything set correctly on this server in regards to Default Regional Settings. I have also gone through the registry and manually changed any incorrect regional settings. However OWA at this one site (other nearly identical sites are ok) displays dates in the email folders in the American format instead of the Australian format. OWA Options have been checked and the time zone is set correctly (it's the only thing other than language that lets you specify your location in OWA). This is driving me crazy. The only difference between this box and another I have...

date format
i would like to change the date format that appears in templates, to the UK format ie day/month/year, any ideas. I think this follows your system settings. Other than that, when you Insert > Field into a shape, you can specify the formatting from the list on the right. In your case you want something like DD.MM.YY. -- Hope this helps, Chris Roth Visio MVP "sadie" <sadie@discussions.microsoft.com> wrote in message news:EEC6D9EE-B70D-4AB9-BC6F-AF24D60EED82@microsoft.com... >i would like to change the date format that appears in templates, to the UK > format i...

Finding Dates in a date range
I have a column in a spreadsheet that consists of dates in dd/mm/yyyy format. I'm trying to write a formula that looks at the dates by row in the colum and if the date falls within a certain range (eg from 01/04/2004 to 01/06/2004) then I want to sum the values in another column. I've worked out that I need to use the sumif function but I can't work out how to analyse the date range. How do I tell the function to look at the dates in the column and if they are within a certain range to sum the values in another column. As I said, I'm pretty sure I need to use the sum...

date formating, creating unique calender
I need a formula that will allow me to cycle thru 10 days and back again. There are 10 levels, starting with level 1. every day that passes, the level goes up by 1, not to go over 10. So every 10 days, the cycle starts over agan. So, if something is at a level 4, and 5 days pass, the level is 9. IF its at a level 4 and 12 days pass, its a level 6.... get it.... HOW DO I DO THIS!!?? I have been working and search for the answer alllll day... please someone help?? On Sun, 28 Feb 2010 14:13:02 -0800, Squeeker <Squeeker@discussions.microsoft.com> wrote: >I need a...

I can not change my hyperlink colors! They always come up in blue!
Could someone please tell me why I cant change the hyperlink color I have tried everything on the help menue but it still does not work! Please Help Thank You Assuming you use Pub 2003, go to Format > Color Schemes > Custom color scheme where you can choose the hyperlink color and the followed hyperlink color. DavidF "Mitchel" <Mitchel@discussions.microsoft.com> wrote in message news:B012240A-45AD-4FBC-A32C-270BC366F3FA@microsoft.com... > Could someone please tell me why I cant change the hyperlink color I have > tried everything on the help menue but it still ...

Date incrimintation
Hello, I want to place a date in header or run a macro to place the date on ever page like this: Thursday, 3.4.2010 and on second page, Friday, 3.5.2010 and on third page Saturday, 3.06.2010 and so on Have anybody an idea how to do it? Thank you! Hi Damian, Unless you use 'Next Page' Section breaks, you can't do it for more than 3 pages. -- Cheers macropod [Microsoft MVP - Word] "Damian" <Damian@discussions.microsoft.com> wrote in message news:8295D461-B278-46AD-AAF5-C297A5CD0A66@microsoft.com... > Hello, > > ...

roll down promised ship date to all items on a purchase order
I would like to be able to put my promised ship date on a purchase order - after all items have been entreed and I have rec'd confirmation from shipper - and have the promised date roll down to all items, instead of having to go into each item and change the promised ship date one at a time. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Mic...

Differentiate between Number and Date?
I'm trying to determine the type of information in each column. I was using the TYPE function but it doesn't differentiate between a Number and a Date. The dates in my cells are either: 3/21/1999 3/21/1999 20:31:56 PM ....both of which Excel reads as a Number (type=1) I want, basically: if(type(A1)=1,"N",if(type(A1)=2,"C"...etc., so that my result in the cell is either N, D, C, or L Any ideas? Merle Ah, and additionally (let's make things really complicated here), not all the cells in a column are populated with data. So, I can't just test the first...

Date settings
You can set various date formats from System Settings in CRM including dd/mm/yyyy. From: "David Mobile" <DavidMobile@discussions.microsoft.com> Subject: Date settings Date: Monday, 20 December 2004 4:05 PM Can date be changed from eg 12-21-2004 to 21 day of 12 month 2004 re 21/12/2004 instead of 12/21/2004? Thankyou -- dave mobile ...

Conditional Formatting
Hi, I have a spreadsheet that is for tracking work. I would like to have conditional formatting so that if the date in column E is between todays date and 4 weeks away then make it blue, if it is a date that is after 4 weeks away then make it yellow but if it is in the past make it red. Can this be done with conditional formatting or does it need vba? Also it would be nice if the whole row changes colour even if just the condition is on the date which is in column E - but if that can't be done then just the cell is fine. Thanks Assume the dates start in E2, select all dates w...

To watch always a column
Hi, I would like to see always the first column. That is, if I scroll and see z column I want to continue watching A column. Is it possible to d it? If so, how? Thanks -- alm ----------------------------------------------------------------------- almu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3326 View this thread: http://www.excelforum.com/showthread.php?threadid=53086 Select cell B1 From the menu bar: Window>Freeze Panes -- Kind regards, Niek Otten "almu" <almu.25w5em_1144408200.8842@excelforum-nospam.com> wrote in message ne...

Changing base date?
I was copying data from an opened CSV into a template and noticed that the all of the dates were wrong! I changed the numeric format to general in both windows to see if the base number was the same. They were I then changed the number to 0 then formatted it as a date and saw one spreadsheet format the zero as "1/0/1900" and the other spreadsheet formatted the zero as "1/1/1904" What gives? Thanks. One computer is using the 1904 date system under tools>options>calculations Regards, Peo Sjoblom "Aaron Bratcher" <aaron@adsgainc.com> wrote i...

Calendaring Out-of-date
I've been getting several people who noticed that their meetings no longer move or delete correctly anymore. On exchange 2k sp4, with Norton EE running. I believe I may have inadvertantly run AV against the M: drive thusly corrupting Calendar or meetings so I ran a repair against all my SG's. Some came back like this below mentioned. I still have the calendar out-of-date errors but also trying to find out what the 205 means too. Thanks in advance. Operation completed successfully with 205 (Unknown Error, Unknown Error) after 1 93.719 seconds. I'm glad I'm not the only on...

Dynamic date function in CRM
I'm customizing the Account form for our company and would like to set up something that would be able to adjust the time range automatically, the request from the user is below: Timing – drop down menu that needs to be able to move over time. Don’t think we really work on stuff that is more than 12 mos out, but to be safe, maybe we should just make it 18 mos. So for example in May, the choices would be Q207, Q307, Q407, Q108, Q208, Q308, Q408. When someone adds a transaction in July, the choices would be from Q307 through and including Q109. is there a built in function that wou...

Display File Date Modified
Hi, How do I display the File Date Modified of the mdb file in a Report. Thanks for your help. Marv Trott FileDateTime(CurrentDb.Name) If you want a text box with that in it, you can set the text box's ControlSource to =FileDateTime(CurrentDb.Name). Alternatively, you can set the ControlSource to something like ="The file was last modified " & FileDateTime(CurrentDb.Name) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Marv" <marvtrott@earthlink.net> wrote in message news:u$vNdF6yHHA.1100@TK2MSFTNGP06.p...

Moving a date forward
I have a worksheet that tracks the processing of bills, that starts wit the invoice date. I am assuming, on average, 3 days in the mail. W don't receive/process the bills on Saturday or Sunday. Assuming that the invoice date is in B1, and the days in the mail is i A1, I'm am using =$A$1+B1. How can I get those that fall on Saturda or Sunday because of the mailing time to Monday? Thanks Sta -- Message posted from http://www.ExcelForum.com Hi try =WORKDAY(B1,A1,list_of_holidays) >-----Original Message----- >I have a worksheet that tracks the processing of bills, that sta...

Howto export onenote order by date
just like title! Please see http://www.officeforlawyers.com/howask.htm -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "spender" <spender@yeah.net> wrote in message news:hqdm7c$rg8$1@www.shinco.com... > just like title! ...

Importing QIF File Produces Wrong Date Order
Just started setting up to use MS Money 2005 Premium. My banking institution(s) does not offer .ofx files for download (Canada) but does offer .qif, however, when I import data from this data file the date is incorrect (month and day reversed). Any suggestions? Does anyone have eta for TD and CIBC to support .ofx? In microsoft.public.money, "Van G" <Van G@discussions.microsoft.com> wrote: >Just started setting up to use MS Money 2005 Premium. > >My banking institution(s) does not offer .ofx files for download (Canada) >but does offer .qif, however, when I im...

NZ Function with Time/Date Field
I am working on a database for calculating worked time. Basically, I am trying to calculate the total time per day. My logic is: SUM(ENDTIME - STARTTIME) - SUM(LUNCHOUT-LUNCHIN). This works great in my query! However, if a user does not take a lunch, then the calculation does not return a value. Is there anyway to default the LUNCHOUT and LUNCHIN to "00:00:00" if there is no value? Similary to SUM(NZ(LUNCHOUT-LUNCHIN), #00:00:00# )) so the calculation can still complete. Any suggestions are appreciated. Thanks! Brad Brad, Are these (EndTime, StartTime, LunchIn, LunchOut) d...

Top 10 Free Dating Web Sites and much more for free.
Top 10 Free Dating Web Sites and much more for free. You want Free Date then go and search on http://www.10sites.uni.cc Every thing you need is Free here on http://www.10sites.uni.cc http://www.10sites.uni.cc ...

date of JPG photo file which is taken
hi, i am using a lot of photos taken at a construction cite,and i have to write the date of JPG file which is taken to excel sheet. how can i get the date VBA property of which is taken at cite. I mean the date of taking photo thanks Go here: http://sourceforge.net/projects/exifclass/ Download the (zipped) source code - extract the "cls" file and import to your VBA project (it's for VB6 but seems to work fine in Excel VBA). Example usage: Sub Tester() Dim exif As New ExifReader exif.Load "D:\Pics\2009\CIMG1730.JPG" Debug.Print...

Text to date????
Is it possible to change a field from text as in 18Feb2000 to a formatted date field ???? I got about 5000 records that needs changing any help appreciated Regards Graham -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspector, the Award Winning Anti-Spam Filter http://mail.giantcompany.com Graham, Use the following formula, where your date is in A2- =DATEVALUE(A2) and format the cell in your required date format. Then "Copy", "Paste Special - Values" to get rid of the formula afterwards if y...

mistaken date entry
I was catching up on balancing my checking account (Money 2005). I accidentally accepted the default date (Oct 8th) when it should have been Aug 8th. How can I go back and change that date so I can continue with the correct date? "IsisTheCat" <isisthecat47removeme@hotmail.com> wrote on 11 Oct 2007 in group microsoft.public.money: > I was catching up on balancing my checking account (Money 2005). I > accidentally accepted the default date (Oct 8th) when it should have > been Aug 8th. How can I go back and change that date so I can > continue with the ...

date conversion 10-28-03
I have text field,which contains date in follow format "Wednesday, August 27, 2003 4:15:57 PM". I need to convert this field in date/time format. Is it possible? Thanks Hi Elaine, Add a date-time field to the table. Then use an update query containing an expression like this to convert the text field to a date/time value to update the new field, where XXX is the name of the text field: CDate(Mid([XXX], Instr([XXX],", ") + 2)) On Tue, 28 Oct 2003 10:51:15 -0800, "Elaine" <anonymous@discussions.microsoft.com> wrote: >I have text field,which contai...

Changing Dates
I am trying to figure out a way to auto-roll the dates on a spreadsheet forward. Basically, what I have is this: A1 B1 C1 D1 09/25 10/01 10/08 10/15 etc... And what I want is for those dates to all roll-forward as they are surpassed, so that on 09/26, the dates will increment one week and show: 10/01 10/08 10/15 10/22 etc, forever. I appreciate any help in sorting this out. ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Far...