Access Access Select Query is cleared out after exporting


Joined
Mar 10, 2014
Messages
3
Reaction score
0
I have a db in Access 2010 and I'm trying to export the results of a SELECT query to an Excel file. I'm saving the export because I eventually want users to be able to run the export through a macro.
When I run the export everything seems to work fine the first time. But, after that I get an error "Query must have at least one destination field."
Then, when I open the query in Design View all tables and fields have been cleared out:confused: In SQL View it just shows: "SELECT;"

So, I open another copy of the backed up db, try again...same thing.:cry: After trying 4 times and having the same thing happen, I turned to google to try to figure out what's causing this. But I can't find anything remotely close and don't really know what to search for!

Any help would be greatly appreciated!
 
Ad

Advertisements

Joined
Mar 10, 2014
Messages
3
Reaction score
0
Yes it is an existing query which is saved and fully functional in the backed up db. All works fine until I run the export.
Initially, I was checking "Export data with formatting and layout".
This morning I decided to try exporting without formatting and it seems to be working fine (I ran it 3 times to be sure the query still remains, and it does:dance:).
But, I'd still like to understand what was causing the query to be cleared out so I can avoid it.

Any ideas>?
 
Joined
Mar 10, 2014
Messages
3
Reaction score
0
SELECT Int_SpecPrjRpt_PreQuery.RES_BUDREF, [resource data].[D or F], [resource data].FAName, [resource data].[Analyst Name], Int_SpecPrjRpt_PreQuery.Description, Int_SpecPrjRpt_PreQuery.CHARTFIELD1, Int_SpecPrjRpt_PreQuery.[Beginning Date], Int_SpecPrjRpt_PreQuery.[Expiration Date], Int_SpecPrjRpt_PreQuery.[Funding Type], Sum(Int_SpecPrjRpt_PreQuery.[1_Carryover]) AS SumOf1_Carryover, Sum(Int_SpecPrjRpt_PreQuery.[2_NewMoney]) AS SumOf2_NewMoney, Sum(Int_SpecPrjRpt_PreQuery.[4_Available Resources]) AS [SumOf4_Available Resources], Sum(Int_SpecPrjRpt_PreQuery.[5_Beg Fund Balance]) AS [SumOf5_Beg Fund Balance], Sum(Int_SpecPrjRpt_PreQuery.[6_Other Restatement]) AS [SumOf6_Other Restatement], Sum(Int_SpecPrjRpt_PreQuery.[7_Adjusted Fund Balance]) AS [SumOf7_Adjusted Fund Balance], Sum(Int_SpecPrjRpt_PreQuery.[8_ContAndTrfr]) AS SumOf8_ContAndTrfr, Sum(Int_SpecPrjRpt_PreQuery.[9_Revenue]) AS SumOf9_Revenue, Sum(Int_SpecPrjRpt_PreQuery.[10_Total Projected Revenue]) AS [SumOf10_Total Projected Revenue], Sum(Int_SpecPrjRpt_PreQuery.[11_Expenditures]) AS SumOf11_Expenditures, Sum(Int_SpecPrjRpt_PreQuery.[11a_Account Receivable]) AS [SumOf11a_Account Receivable], Sum(Int_SpecPrjRpt_PreQuery.[11b_Beg Fund Balance]) AS [SumOf11b_Beg Fund Balance], Sum(Int_SpecPrjRpt_PreQuery.[11c_Other Restatements]) AS [SumOf11c_Other Restatements], Sum(Int_SpecPrjRpt_PreQuery.[11d_AdjFundBal]) AS SumOf11d_AdjFundBal, Sum(Int_SpecPrjRpt_PreQuery.[11e_ContAndTrfr]) AS SumOf11e_ContAndTrfr, Sum(Int_SpecPrjRpt_PreQuery.[11f_Revenue]) AS SumOf11f_Revenue, Sum(Int_SpecPrjRpt_PreQuery.[11g_TotActualRevenue]) AS SumOf11g_TotActualRevenue, Sum(Int_SpecPrjRpt_PreQuery.[11h_Expense]) AS SumOf11h_Expense, Sum(Int_SpecPrjRpt_PreQuery.[12_Beg Fund Balance]) AS [SumOf12_Beg Fund Balance], Sum(Int_SpecPrjRpt_PreQuery.[12a_Other Restatements]) AS [SumOf12a_Other Restatements], Sum(Int_SpecPrjRpt_PreQuery.[12b_AdjFundBal]) AS SumOf12b_AdjFundBal, Sum(Int_SpecPrjRpt_PreQuery.[13_ContrAndTrfr]) AS SumOf13_ContrAndTrfr, Sum(Int_SpecPrjRpt_PreQuery.[14_Revenue]) AS SumOf14_Revenue, Sum(Int_SpecPrjRpt_PreQuery.[15_TotalOperatingRevBudget]) AS SumOf15_TotalOperatingRevBudget, Sum(Int_SpecPrjRpt_PreQuery.[17_Expense]) AS SumOf17_Expense, Sum(Int_SpecPrjRpt_PreQuery.[18_Restricted Balance 9740]) AS [SumOf18_Restricted Balance 9740], Sum(Int_SpecPrjRpt_PreQuery.[19_TotalOperatingExpBudget]) AS SumOf19_TotalOperatingExpBudget
FROM Int_SpecPrjRpt_PreQuery LEFT JOIN [resource data] ON Int_SpecPrjRpt_PreQuery.CHARTFIELD1 = [resource data].Resource
GROUP BY Int_SpecPrjRpt_PreQuery.RES_BUDREF, [resource data].[D or F], [resource data].FAName, [resource data].[Analyst Name], Int_SpecPrjRpt_PreQuery.Description, Int_SpecPrjRpt_PreQuery.CHARTFIELD1, Int_SpecPrjRpt_PreQuery.[Beginning Date], Int_SpecPrjRpt_PreQuery.[Expiration Date], Int_SpecPrjRpt_PreQuery.[Funding Type];
 
Ad

Advertisements

Joined
Mar 20, 2012
Messages
764
Reaction score
4
Off the top of my head, I don't see anything necessarily about the query that should have caused any of your issues, but I'm not the strongest when it comes to SQL, either.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top