TransferSpreadsheet dropping last (Rightmost) column

[bradmaunsell]

Windows XP SP2
Office Pro SP2

I have a problem with TransferSpreadsheet. My code worked fine (for several months) until I added a new column to my export. The newly added column is "ERPEndDate"

If I stop the code run and look at the query, the ERPEndDate is there. However, it does not get included in the genrated Excel file. NOTE: When I stop code and look at the query, the check box for show in query is not checked.

I am stumped - any help appreciated.

Brad
Burlington, VT


Private Sub cmdExportTextTable_Click()
Dim strPath As String
Dim strExportTableName, strExportQueryName, strExcelName, strMM As String
Dim strDestinationTableName, strDestinationExcelName As String
Dim dbs As DAO.Database
Dim qdfNew As DAO.QueryDef

strExportTableName = "Export_" & GetNetworkUserName

strPath = "\\Cambridgesvr\NW Files\PRODUCTION\HUDSON TEXT EXPORTS\"
If (Month(Me.txtEndDate)) < 10 Then
strMM = "0" & (Month(Me.txtEndDate))
Else
strMM = (Month(Me.txtEndDate))
End If

strExcelName = "Cambridge_" & CStr(Year(Me.txtEndDate)) & strMM & "_Policy.xls"
strDestinationExcelName = strPath & strExcelName

Set dbs = CurrentDb()
strSQL = getEXPORT_SQL

Set qdfNew = dbs.CreateQueryDef("qdfNew", strSQL) ' Create QueryDef.
strExportQueryName = "qdfNew"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strExportQueryName, strDestinationExcelName

dbs.QueryDefs.Delete qdfNew.Name ' Delete new QueryDef
dbs.Close
End Sub



Function getEXPORT_SQL() As String
Dim strSQL, strTable As String
strTable = "EXPORT_" & GetNetworkUserName

strSQL = "SELECT " & strTable & ".ProgramNumber, " & strTable & ".PolicyNumber, " & strTable & ".PriorPolicyNumber, " & strTable & ".LegalEntityCode, " & strTable & ".ProducerCode, " & strTable & ".NamedInsuredLast, " & strTable & ".NamedInsuredFirst, " & strTable & ".StreetAddress, " & strTable & ".StreetAddress2, " & strTable & ".City, " & strTable & ".County, " & strTable & ".StateCode, " & strTable & ".ZipCode, " & strTable & ".EffectiveDate, " & strTable & ".ExpirationDate, " & strTable & ".ClaimsMadeRetroactiveDate, " & strTable & ".CancellationDate, " & strTable & ".OriginalInceptionDate, " _
& strTable & ".NewRenewalCode, " & strTable & ".CompanyProductCode1, " & strTable & ".CompanyProductCode2, " & strTable & ".LimitType1, " & strTable & ".Limit1, " & strTable & ".LimitType2, " & strTable & ".Limit2, " & strTable & ".LimitType3, " & strTable & ".Limit3, " & strTable & ".LimitType4, " & strTable & ".Limit4, " & strTable & ".LimitType5, " & strTable & ".Limit5, " & strTable & ".LimitType6, " & strTable & ".Limit6, " & strTable & ".AnnualStatementLineCode1, Sum(" & strTable & ".WrittenPremium1) AS WrittenPremium1, " & strTable & ".AnnualStatementLineCode2, " & strTable & ".WrittenPremium2, " _
& strTable & ".AnnualStatementLineCode3, " _
& strTable & ".WrittenPremium3, " & strTable & ".AnnualStatementLineCode4, " & strTable & ".WrittenPremium4, " & strTable & ".AnnualStatementLineCode5, " & strTable & ".WrittenPremium5, " & strTable & ".AnnualStatementLineCode6, " & strTable & ".WrittenPremium6, " & strTable & ".WrittenExposureAmountType1, " & strTable & ".WrittenExposureAmount1, " & strTable & ".WrittenExposureAmountType2, " & strTable & ".WrittenExposureAmount2, " & strTable & ".WrittenExposureAmountType3, " & strTable & ".WrittenExposureAmount3, " & strTable & ".WrittenExposureAmountType4," _
& strTable & ".WrittenExposureAmount4, " & strTable & ".WrittenExposureAmountType5, " & strTable & ".WrittenExposureAmount5, " & strTable & ".WrittenExposureAmountType6, " & strTable & ".WrittenExposureAmount6, Sum(" & strTable & ".CashApplied) AS CashApplied, " & strTable & ".WriteOff, " & strTable & ".TaxReimbursement, Sum(" & strTable & ".RetailCommission) AS RetailCommission, Sum(" & strTable & ".WholesaleCommission) AS WholesaleCommission, Sum(" & strTable & ".ProgramCommission) AS ProgramCommission, " & strTable & ".FeeType1, " _
& strTable & ".Fee1, " & strTable & ".FeeType2, " & strTable & ".Fee2 , " & strTable & ".FeeType3, " & strTable & ".Fee3, " & strTable & ".FeeType4, " & strTable & ".Fee4 " _
& "FROM " & strTable & " GROUP BY " & strTable & ".ProgramNumber, " & strTable & ".PolicyNumber, " & strTable & ".PriorPolicyNumber, " & strTable & ".LegalEntityCode, " & strTable & ".ProducerCode, " & strTable & ".NamedInsuredLast, " & strTable & ".NamedInsuredFirst, " & strTable & ".StreetAddress, " & strTable & ".StreetAddress2, " & strTable & ".City, " & strTable & ".County, " & strTable & ".StateCode, " & strTable & ".ZipCode, " & strTable & ".EffectiveDate, " & strTable & ".ExpirationDate, " & strTable & ".ClaimsMadeRetroactiveDate, " & strTable & ".CancellationDate, " & strTable & ".OriginalInceptionDate, " _
& strTable & ".NewRenewalCode , " & strTable & ".CompanyProductCode1, " & strTable & ".CompanyProductCode2, " & strTable & ".LimitType1, " & strTable & ".Limit1, " & strTable & ".LimitType2, " & strTable & ".Limit2, " & strTable & ".LimitType3, " & strTable & ".Limit3, " & strTable & ".LimitType4, " & strTable & ".Limit4, " & strTable & ".LimitType5, " & strTable & ".Limit5, " & strTable & ".LimitType6, " & strTable & ".Limit6, " & strTable & ".AnnualStatementLineCode1, " & strTable & ".AnnualStatementLineCode2, " & strTable & ".WrittenPremium2, " & strTable & ".AnnualStatementLineCode3, " _
& strTable & ".WrittenPremium3, " & strTable & ".AnnualStatementLineCode4, " & strTable & ".WrittenPremium4, " & strTable & ".AnnualStatementLineCode5, " & strTable & ".WrittenPremium5, " & strTable & ".AnnualStatementLineCode6, " & strTable & ".WrittenPremium6, " & strTable & ".WrittenExposureAmountType1, " & strTable & ".WrittenExposureAmount1, " & strTable & ".WrittenExposureAmountType2, " & strTable & ".WrittenExposureAmount2, " & strTable & ".WrittenExposureAmountType3, " & strTable & ".WrittenExposureAmount3, " & strTable & ".Fee4, " & strTable & ".WrittenExposureAmountType4, " _
& strTable & ".WrittenExposureAmount4, " & strTable & ".WrittenExposureAmountType5, " & strTable & ".WrittenExposureAmount5, " & strTable & ".WrittenExposureAmountType6, " & strTable & ".WrittenExposureAmount6, " & strTable & ".WriteOff, " & strTable & ".TaxReimbursement, " & strTable & ".FeeType1, " & strTable & ".Fee1, " & strTable & ".FeeType2, " & strTable & ".Fee2, " & strTable & ".FeeType3, " & strTable & ".Fee3, " & strTable & ".FeeType4, " & strTable & ".ERPEndDate " _
& "ORDER BY PolicyNumber"

getEXPORT_SQL = strSQL

End Function

 

[Bogiemon]

ERPEndDate is not in the SELECT part of the query. Only in the GROUP BY part.

 

[bradmaunsell]

Oh-Oh!!!! How did I miss that?

Thanks for the help - it works great when all of the code is included!

Brad