CODEWITHSUNDEEP
ms-accessvba
Michael
Michael

Reputation: 99

Simple Click, Query, and Export - not so simple

Can any one help me set up an Access 2007 VBA query where a visually impaired person could enter a date (Pdate) on a form and result in a query of my table [Donation_Data] to find all records with [Pickup Date] = Pdate and then automatically export the records as an EXCEL file.

Here is what I have so far, but stops at

qd.SQL = "SELECT * FROM " & [Donation_Data] & " WHERE [Pickup Date] = Pdate"

Option Compare Database
Option Explicit

Private Sub Command1_Click()

Dim db As DAO.Database
Set db = CurrentDb   ' use current database

'On Error GoTo Pickup_OnClick_Err

Dim Pdate As Date

Pdate = InputBox("Please Enter the Desired Pickup Date", "")


'develop a query of all Donation_Data records where Pickup Date in Donation_Data table = Pdate

Dim qd As DAO.QueryDef

Set qd = CurrentDb.CreateQueryDef("DonationDataQuery")

qd.SQL = "SELECT * FROM " & [Donation_Data] & " WHERE [Pickup Date] = Pdate"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DonationDataQuery", "C:\Users\dads\Downloads\Donation Data.xlsx", True

Debug.Print qd.SQL

MsgBox "transfered"


End Sub

Based on comment recommendation below, code now looks like this. Query develops beautifully, but no data transfers

Dim db As DAO.Database
Set db = CurrentDb   ' use current database

'On Error GoTo Pickup_OnClick_Err

Dim Pdate As Date

'Pdate = InputBox("Please Enter the Desired Pickup Date", "")


'develop a query of all Donation_Data records where Pickup Date in Donation_Data table = Pdate

Dim qd As DAO.QueryDef

Set qd = CurrentDb.CreateQueryDef("DonationDataQuery")

qd.SQL = "SELECT * FROM [Donation_Data] WHERE [Donation_Data].[Pickup Date] = Pdate"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "DonationDataQuery", "C:\Users\dads\Downloads\Donation Data.xlsm", True

Debug.Print qd.SQL

MsgBox "transfered"


End Sub

Upvotes: 0

Views: 47

Answers (1)

Dane I
Dane I

Reputation: 742

Try this, replace the line in question with (UNTESTED):

qd.SQL = "SELECT * FROM [Donation_Data] WHERE [Donation_Data].[Pickup Date] = Pdate"

Upvotes: 1

Related Questions