[AccessD] This doesn't look right

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

Message created with Bloomba
 
Disclaimer: No tree was killed in the transmission of this message. However, several coulombs of electrons were temporarily inconvenienced.