Update a date's DAY

[roland_access]

I have a database with some 30,000 records in, each with a date in ranging from 01/01/2000 to today. The field is called EnrDate and it is of Short Date format.

I need to update the day of each record's EnrDate to 01 whilst keeping the month and year. How can i do this in an update query or SQL?

Access 2000 btw, thanks.

 

[Jack Cowley]

This code in the Update To: line should do the trick.

DateSerial(Year([mDate]),Month([mDate]),1)

Change mDate to the actual name of your date field. Try this on a backup table to be sure it does what it is supposed to do before you run it on your real table...

hth,
Jack

 

[roland_access]

thanks!

 

[Jack Cowley]

You are welcome! I hope it worked for you....

Jack