Hi Terry
Not sure if this is going to help you, but what I do is not to pas
the date/s as parameters, but to dynamically create the query. It's
really simple ... as follows
Steps
1.) Get the dates (My code generates the start & end date
automatically, because I always report either on calendar
months or on calendar weeks, so it's easy.
2.) Concatenate the date & base query into a single strin
3.) Update the queries .SQL property with the string
4.) Export the Query to Excel (Single Step
Example
'Declaration
Option Compare Databas
Option Explici
Dim R_Date as Date, S_Date as Date, E_Date As Dat
Dim dbsA As DAO.Databas
Dim qdfA As DAO.QueryDe
Dim strSQL As String, FName as Strin
'Set your Query (Which already exists in your Database
Set qdfA = dbsA.QueryDefs("Query Name"
' Set Dates Dynamically ... you would use a MsgBox to return user
' specified dates as follows
' S_Date = InputBox("Please Enter the Start Date", "Start Date"
' You will have to experiment with error trapping, etc to check tha
' you have a valid date. Then you can skip past my dates here
R_Date = Int(Now()
' Set E_Date as just Tuesday Past (assuming you're reporting from
' Tuesday to Monday
Select Case Weekday(R_Date
Case = 1,
E_Date = R_Date - (Weekday(R_Date) + 4
Case Els
E_Date = R_Date - (Weekday(R_Date) - 3
End Selec
' Set S_date 1 week earlier to E_Dat
S_Date = E_Date -
' Create your SQL strin
strSQL = "SELECT * from Table_Name Where (Start_Date >= '" &
Format(S_Date, "dd/mm/yyyy") & "') and End_Date < '" &
Format(E_Date, "dd/mm/yyyy") & "'
' Edit your Quer
qdfA.SQL = strSQ
' Set your Output file name (the excel file
FName = "\\ServerName\ShareNamemsaccess\FileName.xls
'O
'FName = "C:\Subdirectory1\Subdirectory2\FileName.xls
'Export your Query to the Fil
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, &
"Query Name", FName, True, "A1:X65536
' Now carry on with your other cod
Comment
1.) The format of your date in the query is very dependant on what
your data source will accep
2.) This is what I do, I am sure the gurus around have far more
efficient ways of doing this ... I hope they share so I can also
learn
Regards
Peter Belli
Sunny South Afric
----- Terry wrote: ----
I want to automate the creation of weekly reports I
currently prepare manually. The reports are based on
queries which ask for "Start Date" and "End Date". I would
like Access VBA to ask me for "Start Date" and "End Date"
which I would manually enter (into an inputbox), have VBA
pass these parameters to the query (which I don't know how
to do), run the query (which I don't know how to do), and
then save the recordset in an Excel spreadsheet (which I
don't know how to do). Can anybody help, please
Thank you very much
Terry
