Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Unanswered: Query Date Range "Between" "And" statement

    Access 2003

    I have a query with nine fields used. For this discussion I'll just call them A thru I. A thru H are based on yes/no check boxes. I is a date.

    The check boxes are from a form that employees fill out. They are able to check multiple boxes in that particular area of the form. I need to do a tally by quarter to see how many times each box was checked. Each yes/no box is independant of the other.

    The query is setup like this: (sorry for all the periods, but that' the only way to maintain the formatting.)

    ..............A.......B.......C......D.......E.... ..F......G...... H......I
    Criteria....=-1................................................. ..............Between [start date] And [end date]
    Or...................=-.1................................................ ....Between [start date] And [end date]
    ..............................=-1.............................................Betw een [start date] And [end date]
    .....................................=-1......................................Between [start date] And [end date]
    ...............................................=-1............................Between [start date] And [end date]
    .................................................. ....=-1.....................Between [start date] And [end date]
    .................................................. .............=-1............Between [start date] And [end date]
    .................................................. .....................=-1....Between [start date] And [end date]

    As you can see, this requires me to enter the [start date] and [end date] 8 times for each query, which is highly unproductive. It gets even more unproductive when you have three embeded reports in one master report where there are 30 items meaning you have to enter the date range 30 times.

    Is there a way to program Access so that once you enter the date range once, it will automatically reuse if for all subsequent entries in that query??

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Two suggestions....

    Use a form to input the criteria (Start Date and End Date) and reference the form controls in your queries.

    Not sure which, if either of these will work in your situation:
    iif([A] = true,1,0) - do this for A - H
    --or--
    A+B+C+D..... with <0 as criteria

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    This is probably the only circumstance where Access' query designer is more wordy than SQL.

    As soon as you've inserted the object(s) in your query, and made the joins, go into SQL view and add a WHERE clause manually, to wit:

    Code:
    WHERE ((I Between [start date] And [end date]) And (A = True Or B = True ... H = True));
    Saves typing, no?

    Sam
    ps. Beware the parentheses!!!

Posting Permissions

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