I have a query that I export to excel with the code below. The process works
fine, however after it is finished, if I try to close access thru code or
manually, access locks up and I have to use Task List to kill the
msaccess.exe. I can continue to work in access after the export with no
problems, it is only in trying to exit do I have the problem. The query is
complex, 18 queries joined together. Sql is below.
Any ideas?
Query SQL:
SELECT vRDAMAR.ARName AS [Rep Name], vRDAMAR.ARTerr AS [Territory #],
vRDAMAR.AMTerr AS [District #], vRDAMAR.RDTerr AS [Region #],
IIf(IsNull([qryExcelClientCountsOcc]![Count]),0,[qryExcelClientCountsOcc]![Count])
AS Occurred,
IIf(IsNull([qryExcelClientCountssch]![Count]),0,[qryExcelClientCountssch]![Count])
AS Scheduled,
IIf(IsNull([qryExcelClientCountscomp]![Count]),0,[qryExcelClientCountscomp]![Count])
AS Completed,
IIf(IsNull([qryExcelClientCountsclosed]![Count]),0,[qryExcelClientCountsclosed]![Count])
AS Closed,
IIf(IsNull([qryExcelClientCountspend]![Count]),0,[qryExcelClientCountspend]![Count])
AS Pending,
IIf(IsNull([qryExcelClientCountscanceled]![Count]),0,[qryExcelClientCountscanceled]![Count])
AS Canceled,
IIf(IsNull([qryExcelClientCountscclosed]![Count]),0,[qryExcelClientCountscclosed]![Count])
AS [C-Closed],
IIf(IsNull([qryExcelClientCountsccomp]![Count]),0,[qryExcelClientCountsccomp]![Count])
AS [C-Completed],
IIf(IsNull([qryExcelClientCountstotals]![Count]),0,[qryExcelClientCountstotals]![Count])
AS Total,
CCur(IIf([Total]=0,0,IIf(IsNull([qryExcelClientCountsFeePri]![FeePri]),0,[qryExcelClientCountsFeePri]![FeePri])))+IIf(IsNull([qryExcelClientCountsFeeSec]![FeeSec]),0,[qryExcelClientCountsFeeSec]![FeeSec])
AS Fee,
CCur(IIf([Total]=0,0,IIf(IsNull([qryExcelClientCountsHonorPri]![HonorPri]),0,[qryExcelClientCountsHonorPri]![HonorPri])))+IIf(IsNull([qryExcelClientCountsHonorSec]![HonorSec]),0,[qryExcelClientCountsHonorSec]![HonorSec])
AS Honorarium,
CCur(IIf([Total]=0,0,IIf(IsNull([qryExcelClientCountsOOPPri]![OOPPri]),0,[qryExcelClientCountsOOPPri]![OOPPri])))+IIf(IsNull([qryExcelClientCountsOOPSec]![OOPSec]),0,[qryExcelClientCountsOOPSec]![OOPSec])
AS [Out Of Pocket], [Fee]+[Honorarium]+[Out Of Pocket] AS [Total Money],
CCur(IIf([Total]=0,0,IIf(IsNull([qryExcelClientCountsPriYearTotal]![Total]),0,[qryExcelClientCountsPriYearTotal]![Total])))+IIf(IsNull([qryExcelClientCountsSecYearTotal]![TotalSec]),0,[qryExcelClientCountsSecYearTotal]![TotalSec])
AS [Money YTD]
FROM ((((((((((((((((vRDAMAR LEFT JOIN qryExcelClientCountsOcc ON
vRDAMAR.RUID = qryExcelClientCountsOcc.RUID) LEFT JOIN
qryExcelClientCountsSch ON vRDAMAR.RUID = qryExcelClientCountsSch.RUID) LEFT
JOIN qryExcelClientCountsComp ON vRDAMAR.RUID =
qryExcelClientCountsComp.RUID) LEFT JOIN qryExcelClientCountsClosed ON
vRDAMAR.RUID = qryExcelClientCountsClosed.RUID) LEFT JOIN
qryExcelClientCountsCanceled ON vRDAMAR.RUID =
qryExcelClientCountsCanceled.RUID) LEFT JOIN qryExcelClientCountsCClosed ON
vRDAMAR.RUID = qryExcelClientCountsCClosed.RUID) LEFT JOIN
qryExcelClientCountsCComp ON vRDAMAR.RUID = qryExcelClientCountsCComp.RUID)
LEFT JOIN qryExcelClientCountsTotals ON vRDAMAR.RUID =
qryExcelClientCountsTotals.RUID) LEFT JOIN qryExcelClientCountsFeePri ON
vRDAMAR.RUID = qryExcelClientCountsFeePri.RUID) LEFT JOIN
qryExcelClientCountsFeeSec ON vRDAMAR.RUID = qryExcelClientCountsFeeSec.RUID)
LEFT JOIN qryExcelClientCountsHonorPri ON vRDAMAR.RUID =
qryExcelClientCountsHonorPri.RUID) LEFT JOIN qryExcelClientCountsHonorSec ON
vRDAMAR.RUID = qryExcelClientCountsHonorSec.RUID) LEFT JOIN
qryExcelClientCountsOOPPri ON vRDAMAR.RUID = qryExcelClientCountsOOPPri.RUID)
LEFT JOIN qryExcelClientCountsOOPSec ON vRDAMAR.RUID =
qryExcelClientCountsOOPSec.RUID) LEFT JOIN qryExcelClientCountsPriYearTotal
ON vRDAMAR.RUID = qryExcelClientCountsPriYearTotal.RUID) LEFT JOIN
qryExcelClientCountsSecYearTotal ON vRDAMAR.RUID =
qryExcelClientCountsSecYearTotal.RUID) LEFT JOIN qryExcelClientCountsPend ON
vRDAMAR.RUID = qryExcelClientCountsPend.RUID
WHERE (((vRDAMAR.CLUID)=ClientUID()) AND ((vRDAMAR.Type)=1))
ORDER BY vRDAMAR.ARName;
You may want to use the link below to also post in the MS Office Access discussion group.
===========
<<"Derek Howard" <DerekHoward@discussions.microsoft.com> wrote in message
news:0CA494D8-C4A8-4C1F-BACF-DA5EE9A787EB@microsoft.com...
I have a query that I export to excel with the code below. The process works
fine, however after it is finished, if I try to close access thru code or
manually, access locks up and I have to use Task List to kill the
msaccess.exe. I can continue to work in access after the export with no
problems, it is only in trying to exit do I have the problem. The query is
complex, 18 queries joined together. Sql is below.
Any ideas? >>
--
Please let us know if this has helped,
Bob Buckland ?:-)
MS Office System Products MVP
>>*Courtesy is not expensive and can pay big dividends<<
Access 07 
Linear Mode
