By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,425 Members | 2,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,425 IT Pros & Developers. It's quick & easy.

Validation Rule Concerning Current vs Coming year

P: 2
I have an Access 2010 database that I enter purchase orders into. By default when I enter a ship date the program inputs the current year which isn't a big deal until November and December of a given year, at that time the orders I'm entering have a mix of ship dates of the current and the coming year. How can I make the program recognize that when I enter a ship date of say, January whatever, I mean January of the coming year and not the current year?
Dec 17 '13 #1

✓ answered by zmbd

tomdible

By default, all dates entered are assumed by the program and the OS to be for the current year.

Let's take today 2013-12-18 and your example that you are entering a January date. That you want to enter 01/05 (mm/dd) and have the system assume that you mean 2014-01-05 and not 2013-01-05 is only logical if and only if there would never be a time when today (or later) you would not actually need to enter 2013-01-05.

Seth is fairly close in code.
Neopa is correct in that the date needs to be evaluted at the point of entry.

Here's what I would do:
On the form, checkbox, with title: "set all dates from Jan thru October for next year", default unchecked, default hidden (the caption could be changed in the onload event for thru nov if current Month() is dec)

At the declarations level of the form have a boolean for "henpeckonce"

On Load event, check current date Month() if November or December then Checkbox is set true and made visible and "henpeckonce" set to true

Before update event check for the for the control:
1) if the checkbox is visible the proceed to 2 else bail
2) if Henpeck is true and the checkbox is true then msgbox to remind them that the checkbox is toggled true and that dates with Jan thru Oct (with conditionl we can change the string to Jan thru Nov) will be entered for next year, is this OK, use the YES/NO buttons with NO defaulted.
2a) if vbYes, then set Henpeck to false and proceed
2b) if vbNo, then set Henpeck to false, set the checkbox to false, and bail
3) if the checkbox is true then check the date entered, if the Month() Jan thru Oct (or nov if current dec) then check the year:
3a) if year<current then prompt for action
3b) if year=current then +1
3c) if year>current then leave alone
I leave the code for OP

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
I believe that you are talking about the ability to just type in the day and month of the date and then Access fills in the year. If this is the case, then I would try something like the following: In the control's AfterUpdate event, test the value to see if it is less than today's date (using the Date function). If it is, then use the DateAdd function to add 1 year to it. For example, if you control name is txtShipDate, then your code would be something like this:
Expand|Select|Wrap|Line Numbers
  1. If Me.txtShipDate < Date Then
  2.     Me.txtShipDate = DateAdd("yyyy", 1, Me.txtShipDate)
  3. End If
Dec 17 '13 #2

NeoPa
Expert Mod 15k+
P: 31,494
Does this question even concern the Validation Rule property at all? It sounds like probably not.

As Seth says, if you are talking about the default year being added to a date when omitted from the entry data, you will need to check the value input after it's been submitted. Or you can check the characters entered, but that's much more involved.
Dec 18 '13 #3

zmbd
Expert Mod 5K+
P: 5,397
tomdible

By default, all dates entered are assumed by the program and the OS to be for the current year.

Let's take today 2013-12-18 and your example that you are entering a January date. That you want to enter 01/05 (mm/dd) and have the system assume that you mean 2014-01-05 and not 2013-01-05 is only logical if and only if there would never be a time when today (or later) you would not actually need to enter 2013-01-05.

Seth is fairly close in code.
Neopa is correct in that the date needs to be evaluted at the point of entry.

Here's what I would do:
On the form, checkbox, with title: "set all dates from Jan thru October for next year", default unchecked, default hidden (the caption could be changed in the onload event for thru nov if current Month() is dec)

At the declarations level of the form have a boolean for "henpeckonce"

On Load event, check current date Month() if November or December then Checkbox is set true and made visible and "henpeckonce" set to true

Before update event check for the for the control:
1) if the checkbox is visible the proceed to 2 else bail
2) if Henpeck is true and the checkbox is true then msgbox to remind them that the checkbox is toggled true and that dates with Jan thru Oct (with conditionl we can change the string to Jan thru Nov) will be entered for next year, is this OK, use the YES/NO buttons with NO defaulted.
2a) if vbYes, then set Henpeck to false and proceed
2b) if vbNo, then set Henpeck to false, set the checkbox to false, and bail
3) if the checkbox is true then check the date entered, if the Month() Jan thru Oct (or nov if current dec) then check the year:
3a) if year<current then prompt for action
3b) if year=current then +1
3c) if year>current then leave alone
I leave the code for OP
Dec 18 '13 #4

P: 2
Thank you all for your time. It appears the fix is beyond the scope of my ability and having to type the year as needed seems to be the best course of action for us.
Dec 18 '13 #5

zmbd
Expert Mod 5K+
P: 5,397
We're here for you should you like to make the attempt. (^_^)

I've PM'd you a list of reference sites that should help you dive into the deep-end of Access design and programing.... Once you start... you never stop... come join us... become part of those lost and addicted to the VBA dark-side
(insert dark and sinister laughter)

-z
Dec 18 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.