Solved

Create exported XLS from Query

Posted on 2016-10-23
19
46 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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