[AccessD] Re: This doesn't look right

Steven,

To re-engineer it the best way, I would use 2 queries.  One to hold the SQL 
without criteria.  And the second to replace the SQL with what is in the 
first one plus the criteria from the form.  It makes it much cleaner and 
easier to debug later.

Robert

At 10:35 AM 1/29/2004 -0600, you wrote:
>Date: Thu, 29 Jan 2004 10:37:03 -0600
>From: "Steven W. Erbach" <serbach at new.rr.com>
>Subject: [AccessD] This doesn't look right
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID: <20040129103703.1501118476.serbach at new.rr.com>
>Content-Type: text/plain
>
>Dear Group,
>
>I'm "re-factoring" an application that I took over from a developer who's 
>reducing her workload to devote more time to her family. There is a query 
>that looks curious to me and I wonder if you lot could give me some 
>insight into how Access works.
>
>The query creates a SubTotal and has four Criteria rows and, of course, 
>the Total line is activated. The query looks at four fields on an open 
>form (frmReport) which feed values into the query. Two of those fields are 
>txtLogStartDate and txtLogEndDate. The other two are txtCity and txtCounty.
>
>The four Criteria rows look at various combinations of whether txtCity and 
>txtCounty are filled in or not on the form that feeds the query. The 
>ArrivalDate in the query grid looks at the range of dates between 
>txtLogStartDate and txtLogEndDate on the form.
>
>What's odd is that, at first, the ArrivalDate query column had WHERE in 
>the Total line. The County and City columns show Group By. The range of 
>dates is supposed to be the same for every one of the four Criteria 
>rows...BUT with ArrivalDate set to WHERE there was only one Criteria 
>statement in the first row of the four Criteria rows. It looked like this:
>
>Between [Forms]![frmReport]![txtLogStartDate] And 
>[Forms]![frmReport]![txtLogEndDate] Or Between 
>[Forms]![frmReport]![txtLogStartDate] And 
>[Forms]![frmReport]![txtLogEndDate] Or Between 
>[Forms]![frmReport]![txtLogStartDate] And 
>[Forms]![frmReport]![txtLogEndDate] Or Between 
>[Forms]![frmReport]![txtLogStartDate] And [Forms]![frmReport]![txtLogEndDate]
>
>(Sorry for the wrap) That is, that entire thing was in one cell of one row 
>of the Criteria grid. I tried to cut and paste individual "Between" 
>statements into each of the four rows of the Criteria grid; but when I 
>saved the query, closed it, and re-opened it, that huge long 
>multiple-Between statement re-appeared in just the first row of the 
>Criteria grid.
>
>However, when I changed the ArrivalDate Total line to GROUP BY and *then* 
>cut and paste individual Between statements into each row of the Criteria 
>grid, when I saved, closed, and re-opened the query the Between statements 
>stayed where they were; that is, one for each of the four rows of the 
>Criteria grid.
>
>Now, it was obvious to me that the two forms of the query would produce 
>different results, and that turned out to be true. My question is this: 
>when you're putting together a multi-Criteria row query and you want one 
>column to show the same criteria in each row, the natural thing to do is 
>choose WHERE in the Total row. You can test the query while you're 
>designing it and you'll get results. But when you save the query, thinking 
>that you've done your duty, you're actually saving something incorrect 
>since, if you don't look at it again, Access will re-format all those 
>WHERE statements in each row of the Criteria grid into one concatenated 
>statement in one row.
>
>I thought I knew queries pretty well, but this one throws me. In the SQL 
>statement formed when the ArrivalDate showed WHERE, that big long 
>concatenated multi-Between statement wound up in the WHERE clause. (Didn't 
>matter whether the Between statements were in separate criteria rows or 
>not.) But when I reformed the query to split the Between statements into 
>separate Criteria rows and selected GROUP BY for the ArrivalDate column, 
>the Between criteria wound up in the HAVING clause.
>
>I guess it's just an experience thing, right?
>
>Regards,
>
>Steve Erbach
>Scientific Marketing
>Neenah, WI
>920-969-0504