Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

Create exported XLS from Query

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
seamus9909
Asked:
seamus9909
  • 10
  • 9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
seamus9909Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Technology Partners: 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!

 
seamus9909Author Commented:
I am receiving a run time 3828 error when running this.
0
 
Rey Obrero (Capricorn1)Commented:
what version of office are you using?

which line is raising the error?
0
 
seamus9909Author Commented:
Office 2013


 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryebill", "C:\Documents\myexcel.xls", True
0
 
Rey Obrero (Capricorn1)Commented:
the table that you used in query "Qryebill",  does it have a multivalued field?
0
 
seamus9909Author Commented:
Meaning a field that contains multiple values ?
0
 
Rey Obrero (Capricorn1)Commented:
yes
0
 
seamus9909Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you post the result of that query?
0
 
seamus9909Author Commented:
Sure, what type of file would you like to see it in?
0
 
Rey Obrero (Capricorn1)Commented:
before that, I just noticed, is the name of the query correct? "Qryebill"
0
 
seamus9909Author Commented:
Yes that is the name of the Query
0
 
seamus9909Author Commented:
Here you go
QryEbill.xlsx
0
 
Rey Obrero (Capricorn1)Commented:
I am puzzled why you are getting the error...
can you upload a copy of the db?
0
 
seamus9909Author Commented:
its both a FE and BE that you will need to relink.
DMC_Backup_Backup.accdb
DMC_Backup_Backup_be.accdb
0
 
Rey Obrero (Capricorn1)Commented:
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
 
seamus9909Author Commented:
Thanks so much diligence.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now