CODEWITHSUNDEEP
vbams-access
Jason Bayldon
Jason Bayldon

Reputation: 1296

Getting the first and last dates in a month

I have two comboboxes that lets the user select a date and year. What I want to do is to translate this into my query in access, which requires a [start date] and an [end date] parameter.

I.E. user picks "May" and "2013" in the combo boxes

My query is setup between [start date] and [end date], so I want to translate this month and year selection from the combo boxes into two strings (startdate and enddate, then pass them as command parameters) that contain MM/DD/YYYY, MM/DD/YYYY. What is the best way to take two strings and get the first valid day and last valid day. I have:

FirstDayInMonth = DateSerial( _
 Year(Date), Month(Date), 1)

LastDayInMonth = DateSerial( _
 Year(dtmDate), Month(dtmDate) + 1, 0)

But I need to make the switch from a string into a date format to get back the first/last day of the selected month, using only the month ("MAY") and year ("2013")? Am I missing something relatively simple?

Upvotes: 0

Views: 11856

Answers (2)

KekuSemau
KekuSemau

Reputation: 6852

So, do you get the month and year (as integer or long, not a date yet) from the comboboxes?
Then try this: 

Dim m As Long
Dim y As Long
Dim d_from As Date
Dim d_until As Date

m = CLng("01") ' get these two from your comboboxes
y = CLng("2013") 
d_from = DateSerial(y, m, 1)

d_until = DateAdd("m", 1, d_from)
d_until = DateAdd("d", -1, d_until)

When you have the first date, you calculate the second (last day in month) by adding one month, then going one day back.

Upvotes: 2

Gord Thompson
Gord Thompson

Reputation: 123829

Make the "Month" combo box have two columns (Column Count property is 2):

 1 | Jan
 2 | Feb
...
12 | Dec

Set the Bound Column of the combo box to 1 so its .Value is the month number, but display only the month name (hide the number) by setting the width of the first column to zero:

Column Widths: 0";0.75"

Upvotes: 4

Related Questions