Buttons for date ranges 3

[GulfImages]

I knwo this must be real simple but I just have never tried it and can't find anything in a search.

I have some forms with a to and from date fields for criteria. I would like to make some buttons such as This Year, This Month, Last Year, Last Month and write some code that will simply fill in the fields with the to and from dates by clicking the buttons. I just can't figure how to calculate a month or year start and end date based on the date of Now().

Thanks in advance.

 

[ZmrAbdulla]

You could use a date & time picker cotrol or a calendar control on your form.
Use DateDiff() function to calculate.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[PHV]

Have a look at the DateSerial, Year, Month, Day and Now functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

A starting point:
[green]' Last Month[/green]
Me![From date] = DateSerial(Year(Now()), Month(Now()) - 1, 1)
Me![To date] = DateSerial(Year(Now()), Month(Now()), 0)
[green]' Last Year[/green]
Me![From date] = DateSerial(Year(Now()) - 1, 1, 1)
Me![To date] = DateSerial(Year(Now()), 1, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GulfImages]

Thanks guys. Stars for everyone.

For this year I had to do this:

' This Year
Me!Text2 = DateSerial(Year(Now()), 1, 1)
Me!Text4 = DateSerial(Year(Now()), 13, 0)

Seem right? it works, but the 13 seems odd to me.

 

[GulfImages]

Also having some trouble with finding the last day of the current month, I get the first day fine:

DateSerial(Year(Now()), month(Now()), 1)

Just can't figure out how to have it find the last day.

 

[GulfImages]

AhHa, found it from one of your other post PHV:

DateSerial(Year(Now()), 1 + Month(Now()), 0)

You post a lot of great stuff here, thanks!

 

[obli]

I have used a date range input form for a couple years now with pre-set range buttons. I wish I could remember where I got the code so I could give proper credit.


'Year to date button
Private Sub cmdCalYearToDate_Click()
txtFrom = "1/1/" & DatePart("yyyy", DATE)
txtTo = DATE
End Sub

'Month to date button
Private Sub cmdMonthToDate_Click()
txtFrom = DateAdd("d", -(DatePart("d", DATE) - 1), DATE)
txtTo = DATE
End Sub

'Fiscal year to date button
Private Sub cmdProgYearToDate_Click()
If DatePart("m", DATE) > 6 Then
txtFrom = DateSerial(Year(DATE), 7, 1)
Else: txtFrom = DateSerial(Year(DATE) - 1, 7, 1)
End If
txtTo = DATE
End Sub

'Today's date button
Private Sub cmdToday_Click()
txtFrom = DATE
txtTo = DATE
End Sub


'Yesterday's button
Private Sub cmdYesterday_Click()
txtFrom = DateAdd("d", -1, DATE)
txtTo = DateAdd("d", -1, DATE)
End Sub


'Last month's button
Private Sub cmdLastMonth_Click()
txtFrom = DatePart("m", DateAdd("m", -1, DATE)) & "/1/" & DatePart("yyyy", DateAdd("m", -1, DATE))
txtTo = DateAdd("d", -(DatePart("d", DATE)), DATE)
End Sub


'Last fiscal year button
Private Sub cmdLastProgYear_Click()
If Month(DATE) > 6 Then
txtFrom = DateSerial(Year(DATE) - 1, 7, 1)
txtTo = DateSerial(Year(DATE), 6, 30)
Else:
txtFrom = DateSerial(Year(DATE) - 2, 7, 1)
txtTo = DateSerial(Year(DATE) - 1, 6, 30)
End If
End Sub


'Last week's button
Private Sub cmdLastWeek_Click()
Select Case DatePart("w", DATE, vbSunday)
Case 1
txtFrom = DateAdd("d", -7, DATE)
txtTo = DateAdd("d", -1, DATE)
Case 2
txtFrom = DateAdd("d", -8, DATE)
txtTo = DateAdd("d", -2, DATE)
Case 3
txtFrom = DateAdd("d", -9, DATE)
txtTo = DateAdd("d", -3, DATE)
Case 4
txtFrom = DateAdd("d", -10, DATE)
txtTo = DateAdd("d", -4, DATE)
Case 5
txtFrom = DateAdd("d", -11, DATE)
txtTo = DateAdd("d", -5, DATE)
Case 6
txtFrom = DateAdd("d", -12, DATE)
txtTo = DateAdd("d", -6, DATE)
Case 7
txtFrom = DateAdd("d", -13, DATE)
txtTo = DateAdd("d", -7, DATE)
End Select
End Sub

'Last calendar year button
Private Sub cmdLastYear_Click()
txtFrom = "1/1/" & DatePart("yyyy", DateAdd("yyyy", -1, DATE))
txtTo = DateAdd("d", -DatePart("y", DATE), DATE)
End Sub


'This week's button
Private Sub cmdThisWeek_Click()
Select Case DatePart("w", DATE, vbSunday)
Case 1
txtFrom = DATE
txtTo = DateAdd("d", 6, DATE)
Case 2
txtFrom = DateAdd("d", -1, DATE)
txtTo = DateAdd("d", 5, DATE)
Case 3
txtFrom = DateAdd("d", -2, DATE)
txtTo = DateAdd("d", 4, DATE)
Case 4
txtFrom = DateAdd("d", -3, DATE)
txtTo = DateAdd("d", 3, DATE)
Case 5
txtFrom = DateAdd("d", -4, DATE)
txtTo = DateAdd("d", 2, DATE)
Case 6
txtFrom = DateAdd("d", -5, DATE)
txtTo = DateAdd("d", 1, DATE)
Case 7
txtFrom = DateAdd("d", -6, DATE)
txtTo = DATE
End Select
End Sub


'This year's button
Private Sub cmdThisYear_Click()
txtFrom = DateAdd("d", -(DatePart("y", DATE) - 1), DATE)
txtTo = DATE
End Sub

 

[GulfImages]

Great Stuff!

Thanks!