Msaccess query to update day part to 01

[baba]

Hi, I have a date/time column in msaccess 2007 table and it has values in mm/dd/yyyy format. I want to keep the mm and yyyy values as it is and only update the dd value to 01. Is there an easy update query to get it done.
I am tring to run a query like this

 Update Table1 Set Day(ColumnName) = '01'

However I am getting a syntax error on Update statement:


Current value example : 01/23/2012 and expected value 01/01/2012

Current value example: 05/03/2010 and expected value 05/01/2010.


Please let me know your thoughts.

 

[John Big Booty]

Try something along the lines of;

[URL="http://www.techonthenet.com/access/functions/date/dateserial.php"]DateSerial[/URL]([URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("yyyy",YourDateField), DatePart("m",YourDateField), 1)