Jass Saini
asked on
Export Excel File
I am exporting a query into the excel ...I keep getting an error
"File error. Some Number formats may have been lost" .. I added a few more fields to my query and now I am getting this error>
"File error. Some Number formats may have been lost" .. I added a few more fields to my query and now I am getting this error>
ASKER
With hard coding
Private Sub Save_Record_Click()
Call querydef
DoCmd.OutputTo acOutputQuery, "Allot_Q", "Excel97-Excel2003Workbook (*.xls)", "", True, "", , acExportQualityScreen
End Sub
Private Sub Save_Record_Click()
Call querydef
DoCmd.OutputTo acOutputQuery, "Allot_Q", "Excel97-Excel2003Workbook
End Sub
post the codes for this sub routine
Call querydef
Call querydef
ASKER
Public Sub querydef()
Dim db As Database, qdf As querydef, strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Allot_Q")
Analyst = ""
For Each Item In Forms!AllotSearch_frm!lstEmployeeID.ItemsSelected
If Analyst = "" Then
Analyst = "'" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
Else:
Analyst = Analyst & " , '" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
End If
Next
Analyst = "IN(" & Analyst & ")"
Org = ""
For Each Item In Forms!AllotSearch_frm!lstOrg.ItemsSelected
If Org = "" Then
Org = "'" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
Else:
Org = Org & " , '" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
End If
Next
Org = "IN(" & Org & ")"
CostCenter = ""
For Each Item In Forms!AllotSearch_frm!lstCostCenter.ItemsSelected
If CostCenter = "" Then
CostCenter = "'" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
Else:
CostCenter = CostCenter & " , '" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
End If
Next
CostCenter = "IN(" & CostCenter & ")"
Fund = ""
For Each Item In Forms!AllotSearch_frm!lstFund.ItemsSelected
If Fund = "" Then
Fund = "'" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
Else:
Fund = Fund & " , '" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
End If
Next
Fund = "IN(" & Fund & ")"
PEC = ""
For Each Item In Forms!AllotSearch_frm!lstPEC.ItemsSelected
If PEC = "" Then
PEC = "'" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
Else:
PEC = PEC & " , '" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
End If
Next
PEC = "IN(" & PEC & ")"
strSQL = "SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.BC1Chng1, Final_Table.BC1Chng2, Final_Table.BC1Chng3, Final_Table.BC1Chng4, Final_Table.BC1Chng5, Final_Table.BC1Chng6, Final_Table.BC1Chng7, Final_Table.TotalBC1,"
strSQL = strSQL & "Final_Table.BC2Chng2, Final_Table.BC2Chng3, Final_Table.BC2Chng4, Final_Table.BC2Chng5, Final_Table.BC2Chng6, Final_Table.BC2Chng7, Final_Table.TotalBC2, Final_Table.BC3Chng1, Final_Table.BC3Chng2, Final_Table.BC3Chng3, Final_Table.BC3Chng4, Final_Table.BC3Chng5, Final_Table.BC3Chng6, Final_Table.BC3Chng7, Final_Table.TotalBC3, Final_Table.BC4Chng1, Final_Table.BC4Chng2, Final_Table.BC4Chng3, Final_Table.BC4Chng4, Final_Table.BC4Chng5, Final_Table.BC4Chng6, Final_Table.BC4Chng7, Final_Table.TotalBC4, Final_Table.[Adjust Category], Final_Table.Remarks, Final_Table.UpdatedBy, Final_Table.UpdatedDate"
strSQL = strSQL & " FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC & " ORDER BY Final_Table.[Item Number:]"
qdf.SQL = strSQL
End Sub
what are the fields that you added?
did you try my suggestion above?
did you try my suggestion above?
ASKER
I added Final_BC1Chgn1-7, I added the ones that say BC1-4Chng1-7...
This is the portion that is giving me issues as I didn't touch the rest
This is the portion that is giving me issues as I didn't touch the rest
strSQL = "SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.BC1Chng1, Final_Table.BC1Chng2, Final_Table.BC1Chng3, Final_Table.BC1Chng4, Final_Table.BC1Chng5, Final_Table.BC1Chng6, Final_Table.BC1Chng7, Final_Table.TotalBC1,"
strSQL = strSQL & "Final_Table.BC2Chng2, Final_Table.BC2Chng3, Final_Table.BC2Chng4, Final_Table.BC2Chng5, Final_Table.BC2Chng6, Final_Table.BC2Chng7, Final_Table.TotalBC2, Final_Table.BC3Chng1, Final_Table.BC3Chng2, Final_Table.BC3Chng3, Final_Table.BC3Chng4, Final_Table.BC3Chng5, Final_Table.BC3Chng6, Final_Table.BC3Chng7, Final_Table.TotalBC3, Final_Table.BC4Chng1, Final_Table.BC4Chng2, Final_Table.BC4Chng3, Final_Table.BC4Chng4, Final_Table.BC4Chng5, Final_Table.BC4Chng6, Final_Table.BC4Chng7, Final_Table.TotalBC4, Final_Table.[Adjust Category], Final_Table.Remarks, Final_Table.UpdatedBy, Final_Table.UpdatedDate"
strSQL = strSQL & " FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC & " ORDER BY Final_Table.[Item Number:]"
qdf.SQL = strSQL
did you removed what you added and test the export?
post the original strSQL statement
post the original strSQL statement
ASKER
So it's when the string is too long for the row. I am unfamiliar with SQL limitations. The above statement is the orginal after the I started getting the error. I put the fields in one by one..and I didn't get the error until I wrap around to the next line...
ooh, use this formatting
strSQL = "SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org,"
strSQL = strSQL & " dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund,"
strSQL = strSQL & " dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item:],"
strSQL = strSQL & " Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.BC1Chng1, Final_Table.BC1Chng2,"
strSQL = strSQL & " Final_Table.BC1Chng3, Final_Table.BC1Chng4, Final_Table.BC1Chng5, Final_Table.BC1Chng6, Final_Table.BC1Chng7,"
strSQL = strSQL & " Final_Table.TotalBC1,Final_Table.BC2Chng2, Final_Table.BC2Chng3, Final_Table.BC2Chng4,"
strSQL = strSQL & " Final_Table.BC2Chng5, Final_Table.BC2Chng6, Final_Table.BC2Chng7, Final_Table.TotalBC2, Final_Table.BC3Chng1,"
strSQL = strSQL & " Final_Table.BC3Chng2, Final_Table.BC3Chng3, Final_Table.BC3Chng4, Final_Table.BC3Chng5, Final_Table.BC3Chng6,"
strSQL = strSQL & " Final_Table.BC3Chng7, Final_Table.TotalBC3, Final_Table.BC4Chng1, Final_Table.BC4Chng2, Final_Table.BC4Chng3,"
strSQL = strSQL & " Final_Table.BC4Chng4, Final_Table.BC4Chng5, Final_Table.BC4Chng6, Final_Table.BC4Chng7, Final_Table.TotalBC4,"
strSQL = strSQL & " Final_Table.[Adjust Category], Final_Table.Remarks, Final_Table.UpdatedBy, Final_Table.UpdatedDate"
strSQL = strSQL & " FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter)"
strSQL = strSQL & " AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & "
strSQL = strSQL & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & "
strSQL = strSQL & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC & " ORDER BY Final_Table.[Item Number:]"
ASKER
Did that ..but now it's at the end of the SQL statement
qdf.SQL = strSQL
this is where I am getting the error now
qdf.SQL = strSQL
this is where I am getting the error now
add this line
debug.print strSQL
qdf.SQL = strSQL
strSQL will be printed in the immediate Window,
copy the result and Post back here
or
upload a copy of your db..
debug.print strSQL
qdf.SQL = strSQL
strSQL will be printed in the immediate Window,
copy the result and Post back here
or
upload a copy of your db..
ASKER
Hello Rey,
So now I have all that done....I am back to my orginal question..when I export the query into a excel file ...it says
File error. Some Number formats may have been lost.....The number fields in my excel file lose thier format.
So now I have all that done....I am back to my orginal question..when I export the query into a excel file ...it says
File error. Some Number formats may have been lost.....The number fields in my excel file lose thier format.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help
try removing the fields you added, ONE at a time, then test the export, to find out which field is raising the error.
how are you exporting to excel?