Query to show all results from previous month? 2

[mrtauntaun]

I have a query which returns a number of results, one of which a date/time field displaying mm/dd/yyyy hh:mm:ss AM/PM
example, 12/22/2005 5:23:32 PM
I want to make a query that knows what the current month is (but does not needs to show that in anyway), and shows me everything dated the previous month. So if it is feburary, show me everything from january. If march, everything from feb, etc. This is for a report I plan to run once monthly.

 

[DotNetGnat]

use datediff()

-DNG

 

[TwoOdd]

You will need to check both the month and the year when doing your comparison.

Below is a the entire sample query:

SELECT tbl_Sample.* FROM tbl_Sample
WHERE (((Month([fld_ DateInTable]))=Month(DateAdd("m",-1,Date()))) AND ((Year([fld_ DateInTable]))=Year(DateAdd("m",-1,Date()))));

You will want to replace "tbl_Sample" with the name of the table you are querying and specify the field names that you want included in the results if you do not want the entire table.

You'll also want to replace "fld_ DateInTable" with the actual field name in the table.

The WHERE clause has 2 comparisons: First, it looks at the Month of the Date IN THE TABLE and compares it to last month (Take the current month and add -1 (Subtract 1) from it using the DateAdd Function; Second, it looks at the Year of the Date IN THE TABLE and compares it to what year it was last month using the same DateAdd Formula but taking the year value of the results.


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[mrtauntaun]

I am unfamiliar with the Datediff function, but TwoOdd, your solution works perfectly. Thank you so much!

 

[TwoOdd]

After submitting the previous query, I thought that you may want to choose the year and month (or your boss would want to know the results from 3 months ago), so I came up with a query to allow for this:

SELECT tbl_Sample.* FROM tbl_Sample
WHERE (((Month([fld_DateInTable]))=[Month(1-12):]) AND ((Year([fld_DateInTable]))=[Year:]));

Hope this helps

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[mrtauntaun]

Above and beyond the call TwoOdd, thanks again! That one works great too!