Results 1 to 4 of 4
  1. #1
    Jojojo is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    46

    Query Criteria with Multiple Dates

    I am working on a database for a Gym. I am trying to run a Class List Query by Date - and have a factor I can't figure out how to include in the query.



    The User wants to be able to enter a session date (typically 8 weeks) into a form for a particular class and get a class list. I have a form set up so she can choose the class via combo box, enter the start date for the session. The form would then add the 8 weeks and put the end date in for the user. The query will then find everyone in that class who either Registered for it during those dates or whose Membership expires during those dates. And it works.

    for Background: When a client Registers for a class, the user enters the registration date and their renewal date for that membership is automatically calculated by the number of weeks that the membership is. There is also a field where days can be added to the membership as the gym will give extra days/weeks for referrals etc. The Renewal Date formula is
    DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]

    The extra factor is: a member can have days added [AddDays] to their membership. It could be 5, 7, 14 etc. With the way it's set up - it the client registers the day before a new session starts, so for ex. Aug 31 - and then runs the query for Registrations/Renewls from Sept 1 to Oct 31, the query would catch them as their renewal date would end up in the 8 weeks that are queried. However, if they refer someone and have days added, now they get missed off that class list, b/c their registration date is before the dates queried and the renewal date is now longer then the 8 weeks and outside of those 8 weeks.

    Any suggestions on how to include this scenario?

    I figured out how to do multiple OR statements
    Last edited by Jojojo; 10-07-2011 at 04:33 PM. Reason: Solved Myself

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you want to share at least one example with our readers?

  3. #3
    Jojojo is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    46
    Sure,

    In the Design View of the Query, Under the RegistrationDate field I put the following in the critieria to check for anyone who registered during the session dates entered:
    Between [Forms]![Class List by Date Time Form].[StartRegistrationDate] And [Forms]![Class List by Date Time Form].[EndRegistrationDate]

    Then in the OR section (I had to stager, so it wouldn't treat them as an AND) of the RenewalDate field (code: RenewalDate: DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]) I put the following criteria for anyone whose renewal (with the Additional Days added in) came up during the session dates entered:
    Between [Forms]![Class List by Date Time Form].[StartRegistrationDate] And [Forms]![Class List by Date Time Form].[EndRegistrationDate]

    Then I added a 3rd field, calling it OriginalRenewalDate - and this is the code for that: OrigRenewalDate: DateAdd("ww",[MembershipLength],[RegistrationDate]) - it was minus the Add Days - so if their "original" renewal date would come up during those session dates (in case adding the extra days pushed them past the end date) this would also pull those records

    This is what I put in the OR for that one:
    Between [Forms]![Class List by Date Time Form].[StartRegistrationDate] And [Forms]![Class List by Date Time Form].[EndRegistrationDate]

    I wasn't seeing the multiple OR lines when I would open the query until I opened the window more, so I didn't realize I could do that.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great sample. Thanks for sharing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  2. Dates in query criteria
    By thart21 in forum Queries
    Replies: 7
    Last Post: 01-24-2011, 11:56 AM
  3. Dates: query and criteria
    By isnpms in forum Queries
    Replies: 5
    Last Post: 08-22-2010, 08:01 AM
  4. Query multiple field & criteria
    By fua in forum Access
    Replies: 2
    Last Post: 11-04-2009, 08:22 PM
  5. Multiple criteria query
    By DJ-Specter in forum Queries
    Replies: 1
    Last Post: 09-23-2009, 04:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums