Solved

Export Excel File

Posted on 2014-12-22
14
96 Views
Last Modified: 2014-12-23
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>
0
Comment
Question by:Jass Saini
  • 7
  • 7
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40513374
< I added a few more fields to my query and now I am getting this error> >

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?
0
 

Author Comment

by:Jass Saini
ID: 40513384
With hard coding

Private Sub Save_Record_Click()
 
        Call querydef
       
        DoCmd.OutputTo acOutputQuery, "Allot_Q", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityScreen
       
 End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40513390
post the codes for this sub routine

 Call querydef
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Jass Saini
ID: 40513407
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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40513416
what are the fields that you added?
did you try my suggestion above?
0
 

Author Comment

by:Jass Saini
ID: 40513631
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

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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40513641
did you removed what you added and test the export?

post the original strSQL statement
0
 

Author Comment

by:Jass Saini
ID: 40513935
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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40513981
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:]"

Open in new window

0
 

Author Comment

by:Jass Saini
ID: 40515009
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40515045
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..
0
 

Author Comment

by:Jass Saini
ID: 40515218
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.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40515234
@JAss
I can't possibly know what is causing the error until i see the db.

upload a copy of your db.
0
 

Author Closing Comment

by:Jass Saini
ID: 40515461
Thanks for your help
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question