Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Calculations (2000 SR3)

    I need to be able to generate a list of members in a club based on dues paid. These can be paid at any time and members have the option of paying every month, in six month installments or annually. One of the quirks of this organization is that members drop out of membership for some months at a time and are not required to pay for the months they don't show up. This means, that unlike most membership organizations, the membership is fluctuating on a month by month basis and we need to be able to show whose membership is paid up in any given month and year.

    The author of the old DOS-based database which we are porting the info from devised a system that stored this information in a separate table based on an ingenious but arcane code: aA = Jan 1997, aB = Feb 1997, etc. What this means is for someone who pays their fees for the entire year 2005, 12 entries will be recorded in this separate table from iA (jan 2005) to iL (dec 2005). Even though it is a relatively small club, the number of entries is already huge if we want to save historical data and will get bigger as time goes by.

    I have been playing with a more straightforward Receipts table with a ReceiptDetails subtable where membership dues could be recorded with a field for a starting date and an ending date, so Person X paying for just January this year would have a start of Jan 1, 2005 and end of Jan 31, 2005 and Person Y who pays for the whole year would start on Jan 1 and end on Dec 31, 2005.

    I'm not sure whether this is the best approach: If I want to know who is paid up for May this year (only Person Y should show up) I'm not sure how to do the date math with two separate fields in my receipt details.

    I wonder if there is a smarter way to do this with something like DateAdd, Date Diff or DateSerial and recording the start date for the paid up membership and recording the number of months the membership is good for and using that info to derive who is a member in May or June.

    One final twist. There is no historical need to record days of the month and the old system only records month and year. Is there a way to use DatePart and/or input masks and or formats to not have to enter a day of the month in the StartDate field of the receipt or is it mandatory that dates recorded have day, month and year when entered (regardless of how you display them).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Calculations (2000 SR3)

    To start at the end, if you set a Format <code>mmm-yyyy</code> for the date fields, and an Input Mask <code>>L<LL-0000;;_</code>, users will be able to enter a date in the form Apr-2003. It will be stored in the table as the 1st of April, 2003. It is not possible to enter dates with a two digit year this way, because Windows would interpret Apr-03 as the 3rd of April in the current year.

    You could use a start date and end date, or a start date and a duration in months. It is easy to convert from one to the other in a query:
    Duration: DateDiff("m", [StartDate], [EndDate])
    and
    EndDate: DateAdd("m", [Duration], [StartDate])

    To find out who was a member in May, 2004, create a query with crieteria like this:

    <table border=1><td>Field</td><td>StartDate</td><td>EndDate</td><td>Criteria</td><td>< DateAdd("m",1,#May-2004#)</td><td>>= #May-2004#</td></table>
    Note: Access will automatically change May-2004 to the local date format. The expression #May-2004# can be replaced with a reference to a text box on a form, for instance, to make it dynamic.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculations (2000 SR3)

    Works like a charm. As always, I am in your debt! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •