End of month format on input

  • Thread starter Thread starter redFred
  • Start date Start date
R

redFred

My user inputs a date to a form using Access 2007's datepicker. For whatever
day of the month the user selects (in any month), I wish to store the last
day of the month. I display in format MMM YY, but want to store in format
m/d/yy.

Example: User selects 4/12/10; 4/30/10 is stored.


Is there a way to format the input for this and display as I wish it to be?

Thanks.
 
My user inputs a date to a form using Access 2007's datepicker. For whatever
day of the month the user selects (in any month), I wish to store the last
day of the month. I display in format MMM YY, but want to store in format
m/d/yy.

Example: User selects 4/12/10; 4/30/10 is stored.

Is there a way to format the input for this and display as I wish it to be?

Thanks.
Click to expand...

You are confusing how the date is stored with how the date is
displayed.
All dates in an Access Date/Time datatype field are stored as a Number
datatype, double field size. Today is stored as 39891.
How you format it to display, i.e. 3/19/09... Mar 19, 2009, ...
19 March 2009, etc. is irrelevant to how it is stored.

The last day of any month is the 0 day of the following month.
Code the Date control's AfterUpdate event:

Me.[DateField] = DateSerial(Year([DateField]),Month([DateField])+1,0)

Entering today's date, 3/19/2009, in the form's date control, Access
will store it as 39903, which you can then format it to Mar 09 by
setting that control's Format property to MMM YY.
 
That's what I needed. Thanks so much.

fredg said:
My user inputs a date to a form using Access 2007's datepicker. For whatever
day of the month the user selects (in any month), I wish to store the last
day of the month. I display in format MMM YY, but want to store in format
m/d/yy.

Example: User selects 4/12/10; 4/30/10 is stored.

Is there a way to format the input for this and display as I wish it to be?

Thanks.
Click to expand...

You are confusing how the date is stored with how the date is
displayed.
All dates in an Access Date/Time datatype field are stored as a Number
datatype, double field size. Today is stored as 39891.
How you format it to display, i.e. 3/19/09... Mar 19, 2009, ...
19 March 2009, etc. is irrelevant to how it is stored.

The last day of any month is the 0 day of the following month.
Code the Date control's AfterUpdate event:

Me.[DateField] = DateSerial(Year([DateField]),Month([DateField])+1,0)

Entering today's date, 3/19/2009, in the form's date control, Access
will store it as 39903, which you can then format it to Mar 09 by
setting that control's Format property to MMM YY.
Click to expand...
 
Back
Top