Solved

Create exported XLS from Query

Posted on 2016-10-23
19
42 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
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: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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

896 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

11 Experts available now in Live!

Get 1:1 Help Now