Updating Last Day of Month

[Guest]

I have a table that I want to update the rows that have the day that is the
most close to the last day of the month. For example

Row 1 1/15/04
Row 2 1/27/04 Would be updated
Row 3 2/14/04 Would be updated
Row 4 3/2/04
Row 5 3/8/04
Row 6 3/24/04 Would be updated.

 

[John Viescas]

One of many possible solutions:

UPDATE MyTable
SET ...
WHERE MyTable.RecordDate =
(SELECT Max(RecordDate)
FROM MyTable As T2
WHERE Year(T2.RecordDate) = Year(MyTable.RecordDate)
AND Month(T2.RecordDate) = Month(MyTable.RecordDate))

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)