Dates and Queries

[Designer156]

I need to create an expression that will limit the results of my query. I only want to display all records where 'Completed' date falls within the following criteria: Between the 1st April and the end of last month. I do not want to include this month, because by definitiion it is not complete - not a full month.

My head's just a bit mashed and I can hardly begin to work this out.

If you can help me out, I'd appreciate it.

If you have the time I would also like to find the average number of records completed per month as so will need to somehow determine the number of full months between the 1st April this year and today.

Thanks,

Owen.

 

[RuralGuy]

Here's a link you might find useful: A plethora of date functions

 

[pbaldy]

This should help with the first:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604

The DateDiff function should help with the second.

 

[Designer156]

Thanks guys, I'll look into those.

 

[Designer156]

For anyone searching for the solution, the following criteria did it for me:

Between #01/04/2007# And DateSerial(Year(Date()),Month(Date()),0)

and for the number of months passed:

=DateDiff('m',#01/04/2007#,Date())

 

[RuralGuy]

Outstanding Designer156! And you have some links to put in your favorites as well.

 

[pbaldy]

Your link was better RG; I've added it to my bookmarks.

 

[RuralGuy]

Glad you liked it Paul. Stay cool.