Selecting time frame and calculating off Now()

[SMac]

I have a form that a user can select thier criteria to make charts.

One field is "Time Frame" which they can select Current Day, Yesterday, 1
week ago to current, 2 weeks ago to current, 3 weeks ago to current, Previous
month to current, Past 3 months to current, Past 6 months to current, Past 9
months to current and Past year to current. The VB code I have written works
fine until I get to the months, I don't know how to tell it to look at the
1st of the previous month for the the 1 month ago and other months.

If [cmb_Time_Frame] = "Current Day" Then
[RP_Date] = Now()
ElseIf [cmb_Time_Frame] = "Yesterday" Then
[RP_Date] = Now() - 1
ElseIf [cmb_Time_Frame] = "1 week ago to current" Then
[RP_Date] = Now() - 7
ElseIf [cmb_Time_Frame] = "2 weeks ago to current" Then
[RP_Date] = Now() - 14
ElseIf [cmb_Time_Frame] = "3 weeks ago to current" Then
[RP_Date] = Now() - 21
ElseIf [cmb_Time_Frame] = "Previous month to current" Then
?????????
ElseIf [cmb_Time_Frame] = "Past year to current" Then
[RP_Date] = Now() - 366
End If

Help is GREATLY appreciated!!

Thanks.

 

[Allen Browne]

ElseIf [cmb_Time_Frame] = "Previous month to current" Then
Me.[RP_Date] = DateSerial(Year(Date), Month(Date) -1, 1)

 

[SMac]

GREAT!
That worked, thanks.
-Stacey

ElseIf [cmb_Time_Frame] = "Previous month to current" Then
Me.[RP_Date] = DateSerial(Year(Date), Month(Date) -1, 1)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that a user can select thier criteria to make charts.

One field is "Time Frame" which they can select Current Day, Yesterday, 1
week ago to current, 2 weeks ago to current, 3 weeks ago to current,
Previous
month to current, Past 3 months to current, Past 6 months to current, Past
9
months to current and Past year to current. The VB code I have written
works
fine until I get to the months, I don't know how to tell it to look at the
1st of the previous month for the the 1 month ago and other months.

If [cmb_Time_Frame] = "Current Day" Then
[RP_Date] = Now()
ElseIf [cmb_Time_Frame] = "Yesterday" Then
[RP_Date] = Now() - 1
ElseIf [cmb_Time_Frame] = "1 week ago to current" Then
[RP_Date] = Now() - 7
ElseIf [cmb_Time_Frame] = "2 weeks ago to current" Then
[RP_Date] = Now() - 14
ElseIf [cmb_Time_Frame] = "3 weeks ago to current" Then
[RP_Date] = Now() - 21
ElseIf [cmb_Time_Frame] = "Previous month to current" Then
?????????
ElseIf [cmb_Time_Frame] = "Past year to current" Then
[RP_Date] = Now() - 366
End If

Help is GREATLY appreciated!!

Thanks.