Force date to the first day of the month

  • Thread starter Lee Zard via AccessMonster.com
  • Start date
L

Lee Zard via AccessMonster.com

I have a form bound to a table with a text box for clients to enter a date.
The date is formatted to mmmm yyyy because reporting is based only on month
and year. Occasionally a client will enter a date including the day. This
throws off the Group By in my reporting. Is there a way to force the day to
always be 1?
 
K

KARL DEWEY

Occasionally a client will enter a date including the day. Is there a way
to force the day to always be 1?
I would recommend using a DateTime field instead of a text field. I think
that would solve your problem.
 
F

fredg

I have a form bound to a table with a text box for clients to enter a date.
The date is formatted to mmmm yyyy because reporting is based only on month
and year. Occasionally a client will enter a date including the day. This
throws off the Group By in my reporting. Is there a way to force the day to
always be 1?
Click to expand...

On the form, code the date control's AfterUpdate event:
Me.[DateControlName] =
DateSerial(Year([DateControlName]),Month([DateControlName]),1)

All dates, when entered, will be as of the 1st of that entered month.

You'll need to run an update query to change previously entered data.

Update YourTable Set YourTable.[DateField] =
DateSerial(Year(DateField]),Month(DateField]),1)
 
Top