Tags: 2003i, access, based, boxes, call, database, date, fields, microsoft, mysql, oracle, query, range, sql, statement, thru

Query Date Range "Between" "And" statement

On Database » Microsoft Access

2,659 words with 2 Comments; publish: Wed, 12 Dec 2007 10:35:00 GMT; (25046.88, « »)

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.

All Comments

Leave a comment...

  • 2 Comments
    • 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

      #1; Tue, 11 Dec 2007 21:08:00 GMT
    • 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:

      WHERE ((I Between [start date] And [end date]) And (A = True Or B = True ... H = True));

      Saves typing, no?

      Sam

      ps. Beware the parentheses!!!

      #2; Tue, 11 Dec 2007 21:09:00 GMT