Results 1 to 6 of 6
  1. #1
    bab133 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2008
    Posts
    4

    Query using 2 input dates to get rows in between the two input dates

    Hello,



    I have created a database for a Board of Directors - names, photos, contact details, training, etc etc. Each Director is on the Board for a term, let's say 4 years. Technically the Board year is from fiscal year to fiscal year but not every person enters / leaves the Board on the fiscal year start / end date. So I am capturing when the person joined the Board (YYYY-MM-DD, this is the From Date) and then the To Date (YYYY-MM-DD), when the term is to end. This database has current data and historic.

    I want to create a report of the Board of Directors for a particular year. I don't want to create a report per year. I want to create a generic report, where the client will input the Board year they want to report on, then a query will go pick up the particular rows.

    If the client wants a report for the 2013-2014 year, I'd like an input field for the From Date and an input field for the To Date. Then I want this to go to the Board table data and pick up all those records that are within that year. So e.g., Susie might be on Board from 2012-2016. She'll be in the report. Bob is in there from 2009-2013, so he doesn't show up. John from 2014-2018 doesn't show up. etc.

    I have the input pop-ups in the Query right now against the Board table, but can't figure out the criteria statement to pick up the correct rows. Maybe i need a between clause, but that seems to be for 'get me these rows between this date and this date' - but i have 2 dates in my table + the 2 inputs….

    Also, i'd like the client to just put in the fiscal year (YYYY) as an input, but my data is in YYYY-MM-DD. I know I can use parts of a date but i don't want to store this date (the input part of the date) on a form or table anywhere, although i suppose it could be 'stored' in the title of the report itself.

    Oh, finally, i was hoping to do this in a Query or SQL, rather than VB code which i'm not as familiar with.

    Thanks so much!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why aren't Bob and John retrieved? They each have 1 year that matches the criteria range. Do you want only records where criteria range is completely within the member start and end? Susie fits that.

    Are the member start and end dates in date or text fields?

    User should enter year range criteria in unbound textboxes on form. Then code to open report:

    DoCmd.OpenReport "report name", , , Me.tbxBegin & " BETWEEN Year(MembStart) AND Year(MembEnd) AND " & Me.tbxEnd & " BETWEEN Year(MembStart) and Year(MembEnd)"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bab133 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2008
    Posts
    4
    Bob & John aren't retrieved b/c they started or ended before/after the fiscal year end. An input of 2013-2014 means the fiscal year of 2013-2014, which would be e.g., 2013-05-01 to 2014-04-30. Bob ended in 2013, but before the new fiscal year so e.g., 2013-04-30. John started in the new fiscal so started 2014-05-01. This is why they aren't picked up.

    The member start/end dates are in date fields.

    The user enters the start/end dates for the Board terms (From 2014-05-01, To 2018-04-30) in bound fields on the form as this data is required to be stored.

    The user enters whatever fiscal year they want to report on in an input pop-up field (from a query) when selecting a 'Board by Year' report. So the user would then enter From 2013, To 2014.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So this needs to consider the month/day as well as year. User inputs start and end years:

    DateSerial(Me.tbxBegin, 5, 1) & " BETWEEN MembStart AND MembEnd AND " & DateSerial(Me.tbxEnd, 4, 30) & " BETWEEN MembStart AND MembEnd"

    I NEVER use parameter input popups in queries. Cannot validate user input. Preferable is to input on form and query refers to textbox as parameter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    bab133 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2008
    Posts
    4
    Thank you June7.

    You make a good point about the input popups not being validated. I wouldn't want this on my 'day-to-day' input forms though, so are you talking about an input form just for the report? We've never done that, so how does it work? The user selects the Report 'Board by Year' and this pops up an input form for the 2 dates (unbound)? If this is what you're talking about, that seems good to me. So how do i build that (not the form, per se, but the report to open the form)?

    Also, if the 2 inputs on the form are unbound (not saved to a table), how can query refer to the inputs? I guess I'd have to code it (as you have above).... For interest sake, is it possible to do this same kind of thing without coding, just in the query?

    In fact, if i have an input form off the report and then VB code, i could go directly against the table data. I wouldn't even need a query. Would you agree?

    Thanks for your patience. I'm figuring this out bit by bit.... I really appreciate your help!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  2. Input Forms - How To Input Multiple Fields/Records?
    By butterbescotch in forum Forms
    Replies: 1
    Last Post: 04-04-2013, 06:30 AM
  3. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  4. Replies: 10
    Last Post: 12-27-2011, 01:20 PM
  5. Replies: 2
    Last Post: 02-02-2011, 06:39 AM

Tags for this Thread

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