[quote] sql = “SELECT A.MeterNo, A.DateTime, A.MoreVals, A.ReadingID, B.* FROM Readings A, ExtReadings B WHERE A.MoreVals = B.ExtendedID AND “+_
“A.MeterNo = " + Units.RowTag(i) + " AND A.DateTime < '” + d1+”’ AND A.DateTime > '” + d2 + “’ AND A.MoreVals > 0 UNION SELECT A.MeterNo, M” +_
minmax + “(A.DateTime), A.MoreVals, A.ReadingID, B.* FROM Readings A, ExtReadings B WHERE A.MoreVals = B.ExtendedID AND “+_
“A.MeterNo = " + Units.RowTag(i) + " AND A.DateTime >= '” + d3+”’ AND A.DateTime <= '” + d4 +_
“’ AND A.MoreVals > 0 ORDER BY A.ReadingID DESC”[/quote]
Originally this query ended before the UNION and everything worked fine. When I looked at the recordset contents in the debugger, the first field was named
“MeterNo”. Since adding the UNION, the first field and the rest of the fields from the Readings table are all “A.MeterNo”, “A.DateTime”, etc. This would not be a problem except that the code that follows uses either the results of this query or another query on a single table so I cannot make it return a field name starting with “A.” The first time the code encounters something like thisOne = rs.field("MeterNo").StringValue it throws an exception since the field is now “A.MeterNo”. Is there a way to handle this short of duplicating most of my report code?
Make sure they have the name you WANT deliberately rather than just by coincidence
SELECT A.MeterNo as MeterNo, A.DateTime as DateTime, A.MoreVals as MoreVals
etc
.
UNION
SELECT A.MeterNo as MeterNo,
. etc
..Thanks Norman. Works great.