rror: columns in union query do not match

[andrew]

Hi

I am intermittently getting an error, "The number of columns in the two
selected tables or queries of a union query do not match" (VB Error 3307). I
have a Union query (SQL below) which is called both directly and from a
function (VB below). When called directly, the query runs fine. When called
via the function I sometime get the error above, but not always. If I
comment out the VB code the error goes away. Yes, I have carefully checked
that the number of columns is the same in both halves of the union query and
that the columns have the same aliases etc. I can see no reason for this
error and any help would be much appreciated. I am using MS Access 2002 SP3
and Microsoft Visual Basic 6.5


SQL UNION QUERY
----------------
SELECT
SY.lngSystemID,
Nz(SPY.curPartsYear, 0) AS curPartsYearNoDisc,
Nz(SPY.curPartsYear * (100 - TC.intDiscount) / 100, 0) AS curPartsYear,
Nz(TL.curTotalLabour, 0) AS curTotalLabour,
0 AS curPartsVisit,
TC.intDiscount,
Nz(TC.curTravelCost, 0) AS curTravelCost,
Nz(TC.curCallOut, 0) AS curCallOut,
Nz(TC.curAdjustment, 0) AS curAdjustment,
IIf(IsNull(SD.dteDuration), 0, SD.dteDuration) AS dteDuration,
Nz(TL.curTotalLabour, 0) + curPartsVisit + Nz(TC.curTravelCost, 0) +
Nz(TC.curCallOut, 0) AS curServTotal,
Nz(TL.curTotalLabour, 0) + Nz(curPartsVisit, 0) + Nz(TC.curTravelCost,
0) + Nz(TC.curCallOut, 0) + Nz(curAdjustment, 0) AS curTotal,
SY.intServYear,
(curTotal * SY.intServYear) AS curIncomeYear,
Nz(SPY.curServPartsTotal, 0) AS curServPartsTotal,
Nz(curIncomeYear - SPY.curServPartsTotal, 0) AS curProfitYear
FROM (((
qryTravelCost AS TC

INNER JOIN qryServYear AS SY
ON TC.lngSystemID = SY.lngSystemID)

LEFT JOIN qryTotalLabour AS TL
ON SY.lngSystemID = TL.lngSystemID)

LEFT JOIN qryServDuration AS SD
ON SY.lngSystemID = SD.lngSystemID)

LEFT JOIN qryServPartsYear AS SPY
ON TC.lngSystemID = SPY.lngSystemID

WHERE (TC.ysnServSystem = Yes
AND TC.ysnServPlan = No)
OR (TC.ysnServSystem = No
AND TC.ysnServPlan = No
AND TC.ysnPartsOnly = No)

UNION ALL SELECT
SY.lngSystemID,
Nz(SPY.curPartsYear, 0) AS curPartsYearNoDisc,
Nz(SPY.curPartsYear * (100 - TC.intDiscount) / 100, 0) AS curPartsYear,
Nz(TL.curTotalLabour, 0) as curTotalLabour,
Nz((SPY.curPartsYear / SY.intServYear * (100 - TC.intDiscount) / 100),
0) AS curPartsVisit,
TC.intDiscount,
Nz(TC.curTravelCost, 0) AS curTravelCost,
Nz(TC.curCallOut, 0) AS curCallOut,
Nz(TC.curAdjustment, 0) AS curAdjustment,
IIf(IsNull(SD.dteDuration), 0, SD.dteDuration) AS dteDuration,
Nz(TL.curTotalLabour, 0) + curPartsVisit + Nz(TC.curTravelCost, 0) +
Nz(TC.curCallOut, 0) AS curServTotal,
Nz(TL.curTotalLabour, 0) + curPartsVisit + Nz(TC.curTravelCost, 0) +
Nz(TC.curCallOut, 0) + curAdjustment AS curTotal,
SY.intServYear,
(curTotal * SY.intServYear) AS curIncomeYear,
Nz(SPY.curServPartsTotal, 0) AS curServPartsTotal,
Nz(curIncomeYear - SPY.curServPartsTotal, 0) AS curProfitYear
FROM (((
qryTravelCost AS TC

INNER JOIN qryServYear AS SY
ON TC.lngSystemID = SY.lngSystemID)

LEFT JOIN qryTotalLabour AS TL
ON SY.lngSystemID = TL.lngSystemID)

LEFT JOIN qryServDuration AS SD
ON SY.lngSystemID = SD.lngSystemID)

LEFT JOIN qryServPartsYear AS SPY
ON TC.lngSystemID = SPY.lngSystemID

WHERE TC.ysnServPlan = Yes
OR TC.ysnPartsOnly = Yes;



VB FUNCTION - error checking removed
---------------------
Function funCalculations()
'Populates calculated text boxes on this form with values calculated in
union query
'If no matching record exists, sets value to 0.

Dim db As Database
Dim strSQL1 As String
Dim strSQL2 As String
Dim rs1 As Recordset
Dim rs2 As Recordset

If Me.NewRecord = False Then

Set db = CurrentDb
strSQL1 = "SELECT " & _
"S.curTotalLabour , " & _
"S.curPartsYear , " & _
"S.curPartsVisit , " & _
"S.curTravelCost , " & _
"S.curCallOut , " & _
"S.curServTotal , " & _
"S.curTotal , " & _
"S.curIncomeYear , " & _
"S.intDiscount " & _
"FROM qrySystemSub S " & _
"WHERE S.lngSystemID = " & Me.lngSystemID

strSQL2 = "SELECT S.dteDuration " & _
"FROM qrySystemSub S " & _
"INNER JOIN tblInstalledItem I " & _
"ON S.lngSystemID = I.lngSystemID " & _
"WHERE I.ysnService = yes " & _
"AND S.lngSystemID = " & Me.lngSystemID

Set rs1 = db.OpenRecordset(strSQL1)
Set rs2 = db.OpenRecordset(strSQL2)

If rs1.RecordCount > 0 Then
Me.txtLabourVisit = Format(rs1("curTotalLabour"), "0.00")
Me.txtPartsYear = Format(rs1("curPartsYear"), "0.00")
Me.txtPartsVisit = Format(rs1("curPartsVisit"), "0.00")
Me.txtTimeCost = rs1("curTravelCost")
Me.txtCallOut = rs1("curCallOut")
Me.txtSubTotal = Format(rs1("curServTotal"), "0.00")
Me.txtTotal = Format(rs1("curTotal"), "0.00")
Me.txtProfitYear = Format(rs1("curIncomeYear") -
frmSystemServPartsSub.Form!txtTotalCostTotal, "0.00")
Me.txtServDiscount = rs1("intDiscount")
Me.txtDuration = rs2("dteDuration") +
IIf(IsNull(Me.txtTravelTime), 0, Me.txtTravelTime)
End If

Else:
Me.txtLabourVisit = 0
Me.txtPartsYear = 0
Me.txtPartsVisit = 0
Me.txtTimeCost = 0
Me.txtCallOut = 0
Me.txtSubTotal = 0
Me.txtTotal = 0
Me.txtProfitYear = 0
Me.txtServDiscount = 0
Me.txtDuration = 0

End If

End Function