VB Forum / Database Access / June 2009

I have a table called MONTHLY where one of the fields is a date field
called Date.

This field holds the date of the last weekday of each month.

When I create a recordset from this table, I would like to get the
dates to convert to LAST DATE of each month.

For example, if the date happens to be:

1/30/2009 'last weekday of the month

I'd like it to be in my recordset as 1/31/2009

2/27/2009 'last weekday of the month

I'd like it to be 2/28/2009

3/31/2009 to 3/31/2009 'no change, last day

4/30/2009 to 4/30/2009 'no change, last day

5/29/2009 to 5/31/2009

6/30/2009 to 6/30/2009 'no change, last day

7/31/2009 to 7/31/2009 'no change

8/31/2009 to 8/31/2009 'no change

I think you can see what I'm trying to do. All the dates in the
resulting recordset need to be the LAST DAY OF EACH MONTH, not the
last weekday of the month as it is currently in the table.

How would I write a SQL statement to do this?

Thanks.

Webbiz