List boxes with 1st and last day of every month

[Letica]

I want to create a form with 2 list boxes. One list box to list the 1st day
of every month from April 05 to the 1st of previous month when the form is
opened (i.e. 1-Apr-05, 1-May-05, 1-Jun-05,.....1-Nov-08)
Another list box to list the last day of every month to the last day of
previous month when the form is opened.
How can I do that?

 

[Dorian]

You get the current date via function: DATE
Then you use the DATEPART and DATEDIFF functions (look in Access Help for
details)
The first of the month is easy as it is always day 1. The last of the month
is trickier since it can be 28,29,30 or 31. The trick is to get the 1st day
of the next month and then subtract 1 day (using DATEDIFF) function.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Rob Wills]

dim dte as Date

dte = "01 Apr 05"

do until dte > 01 Nov 08

if month(dte) <> Month(Dateadd("D",1,dte)) then
'Add code to add date to Monthend Combo
elseif month(dte) <> month(Dateadd("D",-1,dte)) then
'Add code to add date to monthstart combo
end if

dte = dateadd("D",1,dte)
loop

 

[Beetle]

So when it becomes January 2008, are you going to want your list
boxes to show 1-Dec-08 and 31-Dec-08? This is going to be problematic
to maintain using list boxes. They are going to have to be updated
every month.

What is your ultimate goal? In other words, what will having these list
boxes allow you to do? There may be an easier way.

 

[John W. Vinson]

I want to create a form with 2 list boxes. One list box to list the 1st day
of every month from April 05 to the 1st of previous month when the form is
opened (i.e. 1-Apr-05, 1-May-05, 1-Jun-05,.....1-Nov-08)
Another list box to list the last day of every month to the last day of
previous month when the form is opened.
How can I do that?

One way you could do this is with the help of a little auxiliary table filled
with all the desired dates (you could use just the first of the month); you
can use Excel's Insert... Fill Series to quickly create a table with dates
from 4/1/05 through (say) 4/1/2050, it'll still be a very small table.

Base the first listbox on a query

SELECT DateTable.Datefield FROM DateTable WHERE Datefield <
DateSerial(Year(Date()), Month(Date()) - 1, 1) ORDER BY Datefield;

and the second (end of month) on a query

SELECT DateAdd("d", -1, DateTable.Datefield) FROM DateTable WHERE Datefield <=
DateSerial(Year(Date()), Month(Date()), 1) ORDER BY Datefield;