I posted a plea for help regarding using the date from 1st Setember of one year thru to 1st September of next Year. No answer was the stern reply, so I'm trying to rephrase the question. Does anyone know how to deal with memberships for a club where the date starts in one year but continues to next year for 12 months? I want to be able to find all unpaid members after a certain time expires so that i can chase them up. Any query even when I split the date in a query into seperate fields doesnt seem to work because of the year endings. I dont want to have to type in a parameter asking which year start date, then another parameter asking for the next year end date. Does this make any sense? Or am I making hard work of this?
Put A different way!
- Thread starter Kassy
- Start date
ColinEssex
Old registered user
- Local time
- Today, 20:36
- Joined
- Feb 22, 2002
- Messages
- 9,314
Why do you not want to type in the date parameters?
You know you can have a parameter box pop up when the query is run?
Col
You know you can have a parameter box pop up when the query is run?
Col
How do you want to add the parameter? you can code the end date as being 1 year on from the start date so that you only need to add 1 parameter. You could add it to a form and link to that that way you can set it to default to this year.
If you are storing there last renewal date you should be able to filter against that without needing parameters anyway.
Peter
If you are storing there last renewal date you should be able to filter against that without needing parameters anyway.
Peter
Ok seems good but ...
Do I need a special field in my members table? Presently I have a members table with usual stuff - cant make up my mind whether I need a Lapsed (Yes/No) field in it. I also have a payments table with payment ID and memberID which links obviously to members, via memberID. Memberstbl has a DateJoined Field but this refers to date first joined not the re-registration date. This was inherited. Trying to query this returns everyone with joining date linked to a payment date including old members. I wanted to be able to run queries to find out current members from Sept 1st to Sept 1st , also last years members , and be able to track members who have remained with the club over different years(-history of club). If a member has not paid up his membership by Dec 1st in the current year he is not elegible for special offers in January. If it was Jan to Jan it appears easy enough I Think however Sept Year 05- Sept 06 is causing me problems.
Do I need a special field in my members table? Presently I have a members table with usual stuff - cant make up my mind whether I need a Lapsed (Yes/No) field in it. I also have a payments table with payment ID and memberID which links obviously to members, via memberID. Memberstbl has a DateJoined Field but this refers to date first joined not the re-registration date. This was inherited. Trying to query this returns everyone with joining date linked to a payment date including old members. I wanted to be able to run queries to find out current members from Sept 1st to Sept 1st , also last years members , and be able to track members who have remained with the club over different years(-history of club). If a member has not paid up his membership by Dec 1st in the current year he is not elegible for special offers in January. If it was Jan to Jan it appears easy enough I Think however Sept Year 05- Sept 06 is causing me problems.
Hi -
Placing this in the criteria cell of your date field will resolve the 1 Sep to 31 Aug dilemma. You'll be prompted to enter the (start) year and date parameters will be set.
>=DateSerial([enter year],9,1) And <=DateAdd("yyyy",1,DateSerial([enter year],9,1))-1
HTH - Bob
Placing this in the criteria cell of your date field will resolve the 1 Sep to 31 Aug dilemma. You'll be prompted to enter the (start) year and date parameters will be set.
>=DateSerial([enter year],9,1) And <=DateAdd("yyyy",1,DateSerial([enter year],9,1))-1
HTH - Bob
Last edited:
Sorry Bob -don't work
I have now managed to try your code but I can see now why the code is repeated I get two parameter query boxes asking for 2 dates - I didnt want to do this, I only wanted 1 box -if I had to do it this way, and anyway by typing in 2 year dates i.e. 2004 then next box 2005 I get all recods for these years regardless if the membership was for season 2004-05 or 2005-06 if you see what I mean - it 'doesn't/cant' discern if the membership was for season 1 or season 2 bearing in mind people can join at any time during a year even up to the August, however people paying in August are usually just early for the forthcoming season. Come August people will want to be paying up ready for 2006-07. Then my code would return this seasons members as well as next. I s'pose I need code that takes 1 sep - 31 Aug and instead of calculating on the year date uses days instead (365?)but how to extract the days within two different years and incorporate it into an SQL statement is beyond me. I think if I have to use a parameter box then to make it clear to users I would have to ask which Season rather than year 1 then year 2. Any ideas?
I have now managed to try your code but I can see now why the code is repeated I get two parameter query boxes asking for 2 dates - I didnt want to do this, I only wanted 1 box -if I had to do it this way, and anyway by typing in 2 year dates i.e. 2004 then next box 2005 I get all recods for these years regardless if the membership was for season 2004-05 or 2005-06 if you see what I mean - it 'doesn't/cant' discern if the membership was for season 1 or season 2 bearing in mind people can join at any time during a year even up to the August, however people paying in August are usually just early for the forthcoming season. Come August people will want to be paying up ready for 2006-07. Then my code would return this seasons members as well as next. I s'pose I need code that takes 1 sep - 31 Aug and instead of calculating on the year date uses days instead (365?)but how to extract the days within two different years and incorporate it into an SQL statement is beyond me. I think if I have to use a parameter box then to make it clear to users I would have to ask which Season rather than year 1 then year 2. Any ideas?
My Fault
On looking for the SQL and checking the tables etc I can see that whoever was entering payments details for the memberships has several dates wrong. Some are in the future and the payments for 2006 are for the middle of Setember 2006! So... it seems I have to get the data corrected before I can check this seasons payments. Sorry! Need more investigation first.
On looking for the SQL and checking the tables etc I can see that whoever was entering payments details for the memberships has several dates wrong. Some are in the future and the payments for 2006 are for the middle of Setember 2006! So... it seems I have to get the data corrected before I can check this seasons payments. Sorry! Need more investigation first.
