Discuss "Report 1st of Month in Access" in the Microsoft Access forum.

Hi. I need to produce report on the 15th that would filter all members from the tblMembers whose dueDate for renewal falls on the 1st day of any month.

I have the following fields in the tblMemebers table:
MemberID, JoinDate, Name

And a many to many related table tblMemDateRenew with the following fields:
MemReNewID, MemberID, RenewDate, (dueDate (annually on a monthly basis))

- any assistence would be apprecieated.

Thank you in advance.

jjstccean

I assume that you mean a one-to-many related table, since a many-to-many relationship requires 3 tables. You need to explain a bit more about how the tblMemDateRenew is updated. Is RenewDate updated when a member renews, and does it always reflect the next "1st of the month" that dues must be renewed? And what is the data type of RenewDate, is it "date/time"?

What is confusing me is whether your RenewDate contains nothing but dates that are the 1st of some month, or whether it contains all different days and months and years. Reading your question literally, you asked "..renewal falls on the 1st day of any month." Do you mean that you want to exclude renewals that fall on the 2nd, or the 14th of the month, for instance? That's very different from asking for renewals that will occur between now and the first of next month. Please clarify what you are trying to obtain.

Thank you. The relation is a One to many table :-) MemberID joins the two tables and there are three dates involved.

Joindate in the tblMembers table - in the tblMemDateRenew table we actually have two dates: firstdate which = the joindate in tblMemebers and is updated via Code at the time of recording the new member profile; also the seconddate in tblMemDateRenew which = the seconddate + 12 months.

All dates have the Date/Time Datatype. (all renewals fall on the 1st of a month) eg. joindate = 01/05/2007, firstdate = 01/05/2007, seconddate = 01/05/08...regardless of the "joindate day" if, say joindate/day falls on the 1,4,6 or 9th of the month our system is calculated from the 1st of the month for clarity sake (I hope) and membership renewal is an annual process.

The above example shows the member joined in March. What I hope to achieve, "any time" (day) during a month - is to have a report (clicking a button) showing me all the members due for renewal on 01/06/2008 (SA Time Zone) Then again in June for July, etc.

The JoinDate does not change. Though the FirstDate and SecondDate fields will be altered for the new period next year, and so on.

Thank you very much for your time...

jjstccean.

Honestly, I don't see a need for two additional fields, particularly because they are calculated. You can use functions like DateAdd and DateDiff to calculate the dates within the query.

Having said that, you can use a combination of DateAdd and Month functions to check if a registration is due for renewal within the query.

__________________
jmurrayhead
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

Join our Folding team: DeveloperBarn Folding

I fully agree with jmurrayhead. You have a table structure problem, which I would argue needs to be addressed before trying to do anything else. I can show you the proper table structure, but let me first ask a couple of questions:

1. Do you now or in the foreseeable future need to track renewal history?
2. Do you have a process that only updates the renewal date after payment is received? (My reason for asking is the way you worded your answer about updating: "..will be altered for the new period next year", leaving me unclear as to whether all the dates are just automatically bumped a year.)

Meanwhile, since you always adjust renewals to the 1st of a month, I think what you are searching for is the Access functions dateadd() and/or datepart(). Take a look at Access: DateAdd Function and Access: DatePart Function