Specifying criteria of last month

T

tsison7

I want to print a report which only gathers data from the previous month. If
I use dateserial (year(),month()-1,1) as my starting point I wil get an error
when the month is January.

What's the best way to setup a filter to include only records from last month?
 
R

Rick Brandt

tsison7 said:
I want to print a report which only gathers data from the previous
month. If I use dateserial (year(),month()-1,1) as my starting point
I wil get an error when the month is January.

What's the best way to setup a filter to include only records from
last month?
Click to expand...

No you won't, but that is not the complete expression.

DateSerial(Year(Date()), Month(Date())-1, 1)

DateSerial is fully "year wrap aware" so the zeroth month of one year will
return the 12th month of the previous year.
 
T

tsison7

Thanks. I will use your suggestion but in searching for another option I
tried the following and I'm curious why I'm getting an error.

Private Sub Command12_Click()
'Opens Last Month Summary report sorted alphabetically

Dim strWhereCategory As String
Dim dtStartDate As Date
Dim dtDay As Integer
Dim dtmonth As Integer
Dim dtyear As Integer

If Month(Date) - 1 = 0 Then
dtStartDate = DateSerial((Year(Date) - 1), 12, 1)
Else
dtStartDate = DateSerial((Year(Date)), (Month(Date) - 1), 1)
End If

dtmonth = Month(dtStartDate)
dtyear = Year(dtStartDate)
dtDay = Day(dtStartDate)

strWhereCategory = "[ship date] >= dateserial(dtyear,dtmonth,dtday) and
[ship date] <dateserial (year(),month(),1)
"
title = "Last Month Deliveries"

DoCmd.OpenReport "rptSCSMonthEndSummary", acViewPreview, ,
strWhereCategory
End Sub
 
T

tsison7

I saw an error in my previous post...they should all read with Date()...still
doesn't work though.
--
TIA


tsison7 said:
Thanks. I will use your suggestion but in searching for another option I
tried the following and I'm curious why I'm getting an error.

Private Sub Command12_Click()
'Opens Last Month Summary report sorted alphabetically

Dim strWhereCategory As String
Dim dtStartDate As Date
Dim dtDay As Integer
Dim dtmonth As Integer
Dim dtyear As Integer

If Month(Date) - 1 = 0 Then
dtStartDate = DateSerial((Year(Date) - 1), 12, 1)
Else
dtStartDate = DateSerial((Year(Date)), (Month(Date) - 1), 1)
End If

dtmonth = Month(dtStartDate)
dtyear = Year(dtStartDate)
dtDay = Day(dtStartDate)

strWhereCategory = "[ship date] >= dateserial(dtyear,dtmonth,dtday) and
[ship date] <dateserial (year(),month(),1)
"
title = "Last Month Deliveries"

DoCmd.OpenReport "rptSCSMonthEndSummary", acViewPreview, ,
strWhereCategory
End Sub
--
TIA


Rick Brandt said:
No you won't, but that is not the complete expression.

DateSerial(Year(Date()), Month(Date())-1, 1)

DateSerial is fully "year wrap aware" so the zeroth month of one year will
return the 12th month of the previous year.
Click to expand...
Click to expand...
 
J

John W. Vinson

I want to print a report which only gathers data from the previous month. If
I use dateserial (year(),month()-1,1) as my starting point I wil get an error
when the month is January.

What's the best way to setup a filter to include only records from last month?
Click to expand...
= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)
Click to expand...

will work. DateSerial() is pretty clever - if you use 0 as the month number,
it will figure that you mean December of the previous year. For example:

?dateserial(Year(Date()), 0, 1)
12/1/2006
?dateserial(1900, 1200, 100)
3/9/2000

The Year() and Month() functions require a date argument - I suspect you'll
get an error whenever you run your function as written!

John W. Vinson [MVP]
 
Top