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