Determine the Date Of The Last Day Of Selected Month

[Melissa]

I have this table:
TblProjectYear
ProjectYearID
ProjectYearStartDate
ProjectYearEndDate

The Project Year will always span across December 31; for example 9/1/04 to
6/30/05. How do I build a combobox based on this table that will display all
the months between the StartDate and the EndDate for a given ProjectYearID
and when a selection is made, the full date of the last day of the selected
month is displayed in a textbox? (ie, 4/30/05 if April is selected)

Thanks for all help!

Melissa

 

[Allen Browne]

The dates have to come from somewhere, so create a table that contains one
date field, and enter the last day of each month.

Now create a query that contains both your original table, and the date
table. If you see any line joining the 2 tables in the upper part of the
query design window, select the line and delete it. It is the lack of a join
(a Cartesian Product) that gives you every combination.

Now drag the date field from the date table into the grid. In the Criteria
row under this field enter:
Between [Project].[StartDate] and [Project].[EndDate]
where Project is the name of your table, and StartDate and EndDate are the
names of your fields.

This query generates a record for last date of every month between the
project's starting date and ending date, for each project.

 

[Salad]

I have this table:
TblProjectYear
ProjectYearID
ProjectYearStartDate
ProjectYearEndDate

The Project Year will always span across December 31; for example 9/1/04 to
6/30/05. How do I build a combobox based on this table that will display all
the months between the StartDate and the EndDate for a given ProjectYearID
and when a selection is made, the full date of the last day of the selected
month is displayed in a textbox? (ie, 4/30/05 if April is selected)

Thanks for all help!

Melissa

In a combo box, you can use a value list. I suppose you might want them
in chronological order instead of alpha order too. So you might want to
display month and year.

You could call NewComboList from the OnCurrent event.

Private Sub NewComboList
Dim StrList As String
Dim DatFor As Date

'create list of months (with year concatenated)
'from start date to end date
For DatFor = Me.StartDate To Me.EndDate
strList = Format(Month(datFor),"00") & "/" & Year(datFor) & ";"
'add a month to loop counter
DatFor = DateAdd("m",1,datFor)
Next
Me.ComboField.RowSource = Left(strList,Len(strList)-1)
End Sub


To get the first of the month. You know the month. You know the year
as the listfox will show something like "04/2004". So...use the
DateSerial/DateAdd Function

Dim datX As String
datX = Me.ComboField 'returns a value like 09/2004, 10/2004 etc
'create the first day of the selected month/year.
Me.LastDay = DateSerial(Right(datX,4),Left(datX,2),1)
'get the first day of next month then subtract by 1 to get last day of month
Me.LastDay = DateAdd("m",1,Me.LastDay) -1

 

[Alan Webb]

Melissa,
It's like, not orthodox, but add a column to your table with the month-end
dates you need. Then fill that column with whatever you need. If your
organization has been doing projects for five years and thus far there is
only one row per project year then you will also have to expand this to
sixty rows--one each for all the months there have been projects. Then use
a query as the row source for your combo box that will return your months
based on the project, start and end dates.