Solved

Create exported XLS from Query

Posted on 2016-10-23
19
38 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

Author Comment

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 41857849
yes
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now