Solved

Create exported XLS from Query

Posted on 2016-10-23
19
51 Views
Last Modified: 2016-10-25
1.  I have a created a query that I want to export automatically to an XlS, based on user input via a form.

so, the query  qryebill has a column  "InvoiceBillDate".

The user wants to be able to enter a to and from date and have the query execute and export to a Excel Spreadsheet.

so find all records where InvoiceBilldate is between the to and the from date.

I
0
Comment
Question by:seamus9909
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41856366
in the QBE, place

Field: InvoiceBillDate
Table:

Criteria Between Forms!NameOfForm!txtFromDate  And Forms!NameOfForm!txtToDate


or
Field: InvoiceBillDate                                            | InvoiceBillDate
Table

Criteria >=Forms!NameOfForm!txtFromDate |<= Forms!NameOfForm!txtToDate
0
 

Author Comment

by:seamus9909
ID: 41857429
Thanks Rey.

The next step is to enable the user to enter the To/From Dates, outside of the Query (like on a from) and have the query run and export it to XLS automatically.

Can I use a macro or do I need to write an expression to allow that to happen?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857492
In the form where the user will enter the From-To date, place a command button
place this in the click event of the button

private sub button_click()
if me.txtFromDate & ""<>"" then
   msgbox "Please enter From Date"
  me.txtFromDate.setfocus
  exit sub
end if
if me.txtToDate & ""<>"" then
   msgbox "Please enter To Date"
  me.txtToDate.setfocus
  exit sub
end if

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"QueryName","C:\FolderName\myExcel.xlsx",True

end sub


end sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:seamus9909
ID: 41857821
I am receiving a run time 3828 error when running this.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857824
what version of office are you using?

which line is raising the error?
0
 

Author Comment

by:seamus9909
ID: 41857825
Office 2013


 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryebill", "C:\Documents\myexcel.xls", True
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857837
the table that you used in query "Qryebill",  does it have a multivalued field?
0
 

Author Comment

by:seamus9909
ID: 41857848
Meaning a field that contains multiple values ?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857849
yes
0
 

Author Comment

by:seamus9909
ID: 41857863
I don't think so.

Does this SQl help determine that?

SELECT 233059986 AS [TAX ID], tblClaims.FileNo, tblClaims.InvoiceSentDate, tblClaims.Fname, tblClaims.Lname, tblClaims.[Date of IME], tblClaims.Specialty, tblregions.[USAA Corresponding Region] AS Region, tblStates.Stateabbr, tblClaims.ServiceID, tblClaims.[$Billed], "-" AS TAX, tblClaims.[$Billed] AS TOTAL, tblClaims.Comments, Val(Left([Claim#],9)) AS [Member #], CInt([Lost Report Number]) AS [LOST REPORT #], Right([Claim#],3) AS [Lost Report Number], "PIP" AS PIP, Customers.Company
FROM Customers INNER JOIN ((tblClaims INNER JOIN tblStates ON tblClaims.State = tblStates.ID) INNER JOIN tblregions ON tblStates.Stateabbr = tblregions.[State Code]) ON Customers.ID = tblClaims.[Client - Contact]
WHERE (((tblClaims.InvoiceSentDate) Between [Forms]![Form1]![Text63] And [Forms]![Form1]![text65]) AND ((Customers.Company)="USAA INSURANCE COMPANY"));
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857867
can you post the result of that query?
0
 

Author Comment

by:seamus9909
ID: 41857878
Sure, what type of file would you like to see it in?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857879
before that, I just noticed, is the name of the query correct? "Qryebill"
0
 

Author Comment

by:seamus9909
ID: 41857881
Yes that is the name of the Query
0
 

Author Comment

by:seamus9909
ID: 41857882
Here you go
QryEbill.xlsx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41857892
I am puzzled why you are getting the error...
can you upload a copy of the db?
0
 

Author Comment

by:seamus9909
ID: 41857896
its both a FE and BE that you will need to relink.
DMC_Backup_Backup.accdb
DMC_Backup_Backup_be.accdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41857961
this seems to be a bug..

a work around is to alter the sql statement of the query using vba codes

use this codes to export the query after altering the sql statement

Private Sub Command60_Click()
Dim ssql As String, qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("QryEBill")

ssql = "SELECT 233059986 AS [TAX ID], tblClaims.FileNo, tblClaims.InvoiceSentDate, tblClaims.Fname, tblClaims.Lname," _
    & " tblClaims.[Date of IME], tblClaims.Specialty, tblregions.[USAA Corresponding Region] AS Region," _
    & " tblStates.Stateabbr, tblClaims.ServiceID, tblClaims.[$Billed], ' - ' AS TAX, tblClaims.[$Billed] AS TOTAL," _
    & " Val(Left([Claim#],9)) AS [Member #], CInt([Lost Report Number]) AS [LOST REPORT #]," _
    & " Right([Claim#],3) AS [Lost Report Number], 'PIP' AS PIP, Customers.Company" _
    & " FROM Customers INNER JOIN ((tblClaims INNER JOIN tblStates ON tblClaims.State = tblStates.ID)" _
    & " INNER JOIN tblregions ON tblStates.Stateabbr = tblregions.[State Code])" _
    & " ON Customers.ID = tblClaims.[Client - Contact]" _
    & " WHERE tblClaims.InvoiceSentDate >= #" & [Forms]![Form1]![Text63] & "#" _
    & " And tblClaims.InvoiceSentDate <= #" & [Forms]![Form1]![Text65] & "#" _
    & " AND Customers.Company='USAA INSURANCE COMPANY'"

qd.sql = ssql

 DoCmd.TransferSpreadsheet acExport, 10, "QryEbill", "C:\Users\rey\Downloads\EE\Seamus\myexcel.xlsx", True
 End Sub

Open in new window



another work around is to  create another query from query "QryEBill" and export the created query.
0
 

Author Closing Comment

by:seamus9909
ID: 41859089
Thanks so much diligence.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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