Query to look for previous months data

[Rusty]

Hey Guys,

I've been racking my brains on this one and need your help. I was asked this question by a friend who has just set up a database for a non-profit organisation in the UK. I have attached an simple example database to illustrate the problem.

There is a table called "tblMembers" which contains data on members of clubs and the dates their membership expires.

There is a query called "qryExpiryCrossCheck" and it needs to display people whose membership has expired in the previous month (i.e. May 2005).

Sounds easy? Well the query needs to know that 'now' is June 2005 and that the previous month is May 2005 and then as I said display people whose membership has expired in the previous month.

Any ideas/solutions would be greatly appreciated. As I said this is a favour for a friend who is carrying out some free work for a non-profit organisation.

Cheers,

Rusty

 

[Rusty]

Bloody typical - I hit a deadend and post the problem on the forum, go for a cup of coffee and then I come back and answer my own question!

In case anyone is interested the criteria to put in the date field needs to be:

Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)

Cheers,

Rusty

 

[alwainwright]

Displaying Previous month in report

I've used this formula in a query to look at the previous month's number of phone calls, and used sum on the number of phone calls. I want to run a report on this query each month. How do I get the Date box in the report to say it's for the month of x?

Thanks for your help.

 

[seks69]

I realize this thread is 10 years old but it helped me significantly.
There is one problem with it though.
The way written it would not return the last day of the month.

Original syntax:
Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)

Correct syntax:
Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)