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

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.

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"))

Thanks - both usefull information that helped me along.

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::cool:

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

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

Granville

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.:)

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...

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.