Specifying criteria of last month

  • Thread starter Thread starter tsison7
  • Start date Start date
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?
 
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.
 
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
 
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
Click to expand...

You have your variables inside the quoted string of your WHERE clause. Within
the quotes they will no longer be the names of variables, but just literal
strings of characters.
 
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...
 
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]
 
Back
Top