Query - pull entire prior month (1 Viewer)

[ghh3rd]

I want to select everything in [Date Closed] with a date in the prior month. This would work for the month portion, but I'm having trouble deciding how to handle the year portion.

I don't want all records for Oct in every year, just the current year, and since I'm looking for the prior month, running this in January would cause a problem with grabbing the prior year.

SELECT * FROM qryAdHoc WHERE (((Month([Date Closed]))=Format(Now(),"mm")-1));

Anyone have any ideas?

Thanks,


Randy

 

[MSAccessRookie]

I want to select everything in [Date Closed] with a date in the prior month. This would work for the month portion, but I'm having trouble deciding how to handle the year portion.

I don't want all records for Oct in every year, just the current year, and since I'm looking for the prior month, running this in January would cause a problem with grabbing the prior year.

SELECT * FROM qryAdHoc WHERE (((Month([Date Closed]))=Format(Now(),"mm")-1));

Anyone have any ideas?

Thanks,


Randy

You can use the DateAdd() Function to find the previous month regardless of the year (in January it returns December from the year before).

I believe that you should then be able to compare the Month() for that value AND the Year() for that value against your [DateClosed] to get the Data Set that you want.

 

[Alansidman]

Set a new field in your query. I used Test1. Create this expression.

Test1: IIf(Format([Date Closed],"mm")<10,Mid("00" & Format([Date Closed],"mm"),3) & Format([Date Closed],"yyyy"),Format([Date Closed],"mm") & Format([Date Closed],"yyyy"))

In theCriteria, place the following:

=IIf(Format(Date(),"mm")<10,Mid("00" & Format(Date(),"mm")-1,2) & Format(Date(),"yyyy"),Format(Date(),"mm")-1 & Format(Date(),"yyyy"))

 

[ghh3rd]

Thanks - both usefull information that helped me along.

 

[DCrake]

Here is a set of functions that you may find helpful

Function LastDayOfMonth(AnyDate As Date) As Date
    LastDayOfMonth = DateAdd("d", -1, FirstOfNextMonth(AnyDate))
End Function

Function EndOfLastMonth(Optional dtmDate As Date = 0) As Date
   Dim Msg As String
On Local Error GoTo EndOfLastMonth_Err

    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
  EndOfLastMonth = DateSerial(Year(dtmDate), Month(dtmDate), 0)

EndOfLastMonth_End:
   Exit Function

EndOfLastMonth_Err:
   Msg = "Error #: " & Format$(Err.Number) & vbCrLf
   Msg = Msg & Err.Description
   MsgBox Msg, vbInformation, "EndOfLastMonth"
   Resume EndOfLastMonth_End

End Function

Function FirstOfLastMonth(Optional dtmDate As Date = 0) As Date
   Dim Msg As String
On Local Error GoTo FirstOfLastMonth_Err

    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    FirstOfLastMonth = DateSerial(Year(dtmDate), Month(dtmDate) - 1, 1)

FirstOfLastMonth_End:
   Exit Function

FirstOfLastMonth_Err:
   Msg = "Error #: " & Format$(Err.Number) & vbCrLf
   Msg = Msg & Err.Description
   MsgBox Msg, vbInformation, "FirstOfLastMonth"
   Resume FirstOfLastMonth_End

End Function

Function FirstOfNextMonth(Optional dtmDate As Date = 0) As Date
   Dim Msg As String
On Local Error GoTo FirstOfNextMonth_Err

    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    FirstOfNextMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 1)

FirstOfNextMonth_End:
   Exit Function

FirstOfNextMonth_Err:
   Msg = "Error #: " & Format$(Err.Number) & vbCrLf
   Msg = Msg & Err.Description
   MsgBox Msg, vbInformation, "FirstOfNextMonth"
   Resume FirstOfNextMonth_End

End Function

Function FirstOfThisMonth(Optional dtmDate As Date = 0) As Date
   Dim Msg As String
On Local Error GoTo FirstOfThisMonth_Err

    If dtmDate = 0 Then
        dtmDate = Date
    End If
    
    FirstOfThisMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

FirstOfThisMonth_End:
   Exit Function

FirstOfThisMonth_Err:
   Msg = "Error #: " & Format$(Err.Number) & vbCrLf
   Msg = Msg & Err.Description
   MsgBox Msg, vbInformation, "FirstOfThisMonth"
   Resume FirstOfThisMonth_End

End Function

For your solution you would use the following criteria

Between FirstOfLastMonth() And EndOfLastMonth()

CodeMaster:

 

[namliam]

Same but different....

LastOfLastMonth:
Date() - day(date())

FirstOfLastMonth:
Date() - day(date()) - day(Date() - day(date()) ) +1
or
DateAdd("M",-1,Date() - day(date()) + 1) ' Easier for "multiple" months back

FirstOfThisMonth:
Date() - day(date()) + 1

LastOfThisMonth:
Dateadd("M",1,Date() - day(date()) + 1)+1

 

[ghh3rd]

Thanks everyone -- I've taken away a lot of good stuff from this thread!

Granville

 

[kicker]

I sorry, I could use such solution, but I am lost in all this information (since I'm new in Access).

Could anyone upload a sample for such solution, a simple db that I could copy the things that I really need.



Thnax a lot.

 

[namliam]

Could anyone upload a sample for such solution, a simple db that I could copy the things that I really need.

Kicker, I think the code given in this thread is as simple as it can get.
You can copy/paste it directly into any database...

 

[raskew]

Hi -

I agree with Namliam -- you've been provided a bunch of good advice. Just to add to your confusion, try copying/pasting this to the criteria cell of your query's [DateClosed] field:

between dateserial(year(date()), month(date())-1, 1) AND dateserial(year(date()), month(date()), 0)

It will return all records from the month previous to today's date.

If anyone questions how this will behave if today's date is in January (thus a prior month being in the previous year), here are examples from the debug (immediate) window:

x = #1/13/08#
'first day of prior month:
? dateserial(year(x), month(x)-1,1)
12/1/2007

'last day of prior month:
? dateserial(year(x), month(x), 0)
12/31/2007

Bob

Added: Note: This will work with both dates and text.

 

[v3rse]

Definitly helpful, i will keep that set for sure. Thanks for this post!

Here is a set of functions that you may find helpful


CodeMaster: