I am trying to allow user to enter date (MMDDYYYY) in a form but I want Access to automatically populate the last day of the month when the user enters the Month (MM) and Year (YYYY) e.g user enters 02/2003 and Access returns 02/28/2003. Can this be done through the input mask of the field, or would it require VBA code? Either way if someone can assist I would greatly appreciate it.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
auto populate the last day of month in the Date field
- Thread starter only412c
- Start date
Ok, there are a couple things to consider here.
1) when you enter just a MONTH and YEAR component of a date/time datatype, Access set the DAY part of it to the first of that month. There's not much you can do about that.
2) I don't think you can specifically handle this with an input mask.
3) What you CAN do, is check to see if the user entered just "02/2003", for example, and had the system convert it to "02/01/2003", and if so, then update the value to "02/28/2003". The thing of it is, that if your user actually enters "02/01/2003", you don't want THAT changed, I assume.
The problem seems to be, and I just checked this, that Access sets the DAY guy in there pretty dam' soon - before the BEFORE UPDATE event. So you need to catch it BEFORE the "before update" event - but that's the first event that fires when you leave a control.
So you appear to need to check things before you leave the control..but how? How will Access know you're done entering?
To tell the truth, what I would do is shove a small command button next to the text box, with a caption of "Last Day of Month", and when it's clicked, set the value in the text box to
DATESERIAL(YEAR(date), MONTH(date)+1,1)-1
This will set it to the last day of othe current month.
It may seem hokey and kludgy,but it's just one mouse click for the user, and one line of code for you..sometimes one sacrifices elegance for functionality..
JMH
Me? Ambivalent? Well, yes and no....
Another free Access forum:
More Access stuff at
1) when you enter just a MONTH and YEAR component of a date/time datatype, Access set the DAY part of it to the first of that month. There's not much you can do about that.
2) I don't think you can specifically handle this with an input mask.
3) What you CAN do, is check to see if the user entered just "02/2003", for example, and had the system convert it to "02/01/2003", and if so, then update the value to "02/28/2003". The thing of it is, that if your user actually enters "02/01/2003", you don't want THAT changed, I assume.
The problem seems to be, and I just checked this, that Access sets the DAY guy in there pretty dam' soon - before the BEFORE UPDATE event. So you need to catch it BEFORE the "before update" event - but that's the first event that fires when you leave a control.
So you appear to need to check things before you leave the control..but how? How will Access know you're done entering?
To tell the truth, what I would do is shove a small command button next to the text box, with a caption of "Last Day of Month", and when it's clicked, set the value in the text box to
DATESERIAL(YEAR(date), MONTH(date)+1,1)-1
This will set it to the last day of othe current month.
It may seem hokey and kludgy,but it's just one mouse click for the user, and one line of code for you..sometimes one sacrifices elegance for functionality..
JMH
Me? Ambivalent? Well, yes and no....
Another free Access forum:
More Access stuff at
- Status
Similar threads
- Locked
- Question
- Locked
- Question