Counting records with start date between two dates

[Guest]

I have a membership database where it lists the member ID and the start and
end dates of their membership. I need to run a query to count the number of
members in a certain date range by month. (for the last 6 months or year)
The date range will be entered into a form. How do I get this to work?

Example: member started 1/1/05 and is still current, I need to run a member
count for the last 6 months. How do I get the member to show up for all of
those 6 months?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you mean active members or inactive members?

Assuming the Membership End Date is NULL when the member is still
active:

WHERE (start_date <= Forms!myForm!EndDate AND end_date IS NULL )

OR end_date BETWEEN Forms!myForm!StartDate
And Forms!myForm!EndDate

OR (start_date <= Forms!myForm!StartDate
AND end_date >= Forms!myForm!EndDate)

The first line gets all members who are currently active and started
before the criteria end date, which means they were active in the time
period on the form.

The second line gets all members who are no longer active, but were
active in the time period entered on the form.

The third line gets all members who were active during the time period
entered on the form and became inactive after the form's EndDate.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9VVD4echKqOuFEgEQLZBQCg3jURmqQqiOvmwReJVIFQW346bg0AniP1
gayxGPvhWfeR9dkTlxr4lPsu
=EKot
-----END PGP SIGNATURE-----

 

[Guest]

It's active members, and thanks for the help. I'll try it out his afternoon
and see if it takes care of the whole probem.

 

[Guest]

That solved part of the problem, but there's still another issue. Once I get
the list of all current members, I need to produce a report that shows a
count by month by group for a selected date range.

Example:

Jan 05 Feb 05 Mar 05
Group 1 2000 2463 1980

The members aren't stored by month, there's just a start date and end date
for membership, and it's not the same set of months every time. How can I
get that count to work? I don't know a lot about modules and macros, but I'm
willing to try it to get this to work!