End of month format on input

[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.

 

[fredg]

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.

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.

 

[redFred]

That's what I needed. Thanks so much.

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.

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.