First and Last Days from "Month"

[Guest]

I am using Access 2003. I am trying to set up a Report based on a query
where the user can enter the "Month Year" (ie August 2006) in an input box,
and my query will take that entry and set up the search criteria for data
using the "Date_Field". I would want the search criteria to include every
day in that month (ie: Between #8/1/2006# And #8/31/2006#). I can easily do
this with two input boxes, and the user can input the first date and the
ending date, but I am trying to make it easier. Is this possible?

My other option on the input box would to have the user enter any day within
the month he wants, and the program can determine what the month is and how
many days are in it (ie: The use enters 8-25-06, and the program figures out
that the beggining date is 8-1-06 and the ending date is 8-31-06)

Thanks.

 

[Douglas J. Steele]

If you know a given date, the first of the month is
DateSerial(Year([GivenDate]), Month([GivenDate]), 1), and the last day of
the month is DateSerial(Year([GivenDate]), Month([GivenDate]) + 1, 0)

 

[Guest]

Interesting question. What I would do is to use a combo box which lists the
months that the user can choose from which would be more user friendly. If
you want to try this first create a combo box on your form and call it
cboMonth. Set the Row Source Type property to Value List, set the Format
property to mmmm yyyy, set the List Rows property to 13, Set the Limit To
List property to Yes and change the combo label to 'Select Month' or whatever
is appropriate.

Then paste the function code below into the form's VBA module.

Public Function SetDateRange(vStartMonth As Long, vTotalMonth As Long) As
String

'Return date range for selected combo box
'Entry (vStartMonth) = Number of months previous to current month
' (vTotalMonth) = Number of months to show
'Exit (SetDateRange) = Date range for combo RowSource property

Dim vDate As Date, vList As String, vCount As Long

vDate = "1/" & Month(Date) & "/" & Year(Date) 'calc 1st of
this month
vDate = DateAdd("m", -vStartMonth, vDate) 'set start date
For vCount = 1 To vTotalMonth 'count months
vList = vList & Format(vDate, "mmmm yyyy") & ";" 'copy new date
to list
vDate = DateAdd("m", 1, vDate) 'add 1 month to
date
Next
SetDateRange = Left(vList, Len(vList) - 1) 'return combo
list (less last

End Function

In the Open event of the form enter the following code :-

Dim vDate As Date

vDate = "1/" & Month(Date) & "/" & Year(Date) 'calc 1st of
this month
cboMonth = vDate 'and copy to combo
cboMonth.RowSource = SetDateRange(6, 13) 'set RowSource
to +-6 months

This displays the current date (as Month Year) in the combo box and shows
all months from 6 months ago to 6 months ahead (i.e. 13 months in total) in
the drop down list. Change the 6, 13 for whatever range you want. If you want
hundreds of months then this might not be the best solution. Note that the
function code is generic so you could place it in a Code Module if you wanted
to use it in other forms.

As you are in the US (I think) you will need to swap the Day/Month values in
the two lines that create the dates, something like :-

vDate = Month(Date) & "/1/" & Year(Date) 'calc 1st of this month

I think. You may need to check this out though.

The combo box will always return the first of the selected month, you can
then use Douglas's code to calculate the last day of the month to give you
your actual date range.

As for entering an individual date you could use a similar technique with a
combo box to show all the dates of the selected month for the user to choose
from or just a Text box with the cboMonth combo box to create a specific date.

Hope that helps.

 

[Guest]

That worked perfectly. What would be the format for the first day of the
year and the last day of the year if you know the given year?

If you know a given date, the first of the month is
DateSerial(Year([GivenDate]), Month([GivenDate]), 1), and the last day of
the month is DateSerial(Year([GivenDate]), Month([GivenDate]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am using Access 2003. I am trying to set up a Report based on a query
where the user can enter the "Month Year" (ie August 2006) in an input
box,
and my query will take that entry and set up the search criteria for data
using the "Date_Field". I would want the search criteria to include every
day in that month (ie: Between #8/1/2006# And #8/31/2006#). I can easily
do
this with two input boxes, and the user can input the first date and the
ending date, but I am trying to make it easier. Is this possible?

My other option on the input box would to have the user enter any day
within
the month he wants, and the program can determine what the month is and
how
many days are in it (ie: The use enters 8-25-06, and the program figures
out
that the beggining date is 8-1-06 and the ending date is 8-31-06)

Thanks.

 

[Douglas J. Steele]

First day of the year would be DateSerial(GivenYear, 1, 1). Last day would
be DateSerial(GivenYear, 12, 31)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked perfectly. What would be the format for the first day of the
year and the last day of the year if you know the given year?

If you know a given date, the first of the month is
DateSerial(Year([GivenDate]), Month([GivenDate]), 1), and the last day of
the month is DateSerial(Year([GivenDate]), Month([GivenDate]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am using Access 2003. I am trying to set up a Report based on a query
where the user can enter the "Month Year" (ie August 2006) in an input
box,
and my query will take that entry and set up the search criteria for
data
using the "Date_Field". I would want the search criteria to include
every
day in that month (ie: Between #8/1/2006# And #8/31/2006#). I can
easily
do
this with two input boxes, and the user can input the first date and
the
ending date, but I am trying to make it easier. Is this possible?

My other option on the input box would to have the user enter any day
within
the month he wants, and the program can determine what the month is
and
how
many days are in it (ie: The use enters 8-25-06, and the program
figures
out
that the beggining date is 8-1-06 and the ending date is 8-31-06)

Thanks.