export button

[awake2424]

I am trying to create a button on an access form that will prompt the user to enter a search month and then export to excel all records in that database that match that month.

So, if the user prompt "what month" is October, then THE 30 records in the field [receivedate] that are in october (10/XX/XXXX) are exported to excel. Thanks .

 

[spikepl]

You forgot to mention what your issue is.

 

[smig]

What is the question exactly:
1. How to find the records that are in the selected month ? Create a query using
month(receivedate) as criteria.
2. How to export to excel ? Query can be exported to excel using transferSpredsheet routine

 

[awake2424]

Public Sub ExportQuery1ToExcel()
Const FILE_PATH As String = "C:\Users\cmccabe\Desktop\"
Dim FullPath As String
    strFullPath = FILE_PATH
    DoCmd.TransferSpreadsheet acExport, , "TAT Query", strFullPath & "TAT.xlsx", False
    MsgBox ("Export is complete.")
End Sub

Is this correct and how does the user get prompt for the month to use in the query? So if there are 100 records in the form, the prompt would return only those 30 where the [date_received] in October. Thanks .

 

[awake2424]

Below is the TAT SQL:

 SELECT [HLA TAT].[HLA ID], [HLA TAT].[Last Name], [HLA TAT].[First Name], [HLA TAT].[Date Received]
FROM [HLA TAT]
WHERE ((([HLA TAT].[HLA ID])=[Date Received]));

Thanks.

 

[smig]

If you use code to create the query you can use input box to get the desired month.

If you use the query builder put month(receivedate) as one of the fields, and [AskForMonth] as the criteria of that field.
When you try to run the query you will be prompted for AskForMonth.

The best way will be to create a simple form where the user select a month and click a button to export.
If you use code for the query use Me.fldSelectedMonth as the criteria.
In the query builder the criteria will be [frmMonthSelect].[fldSelectedMonth]

 

[awake2424]

TAT Query:

SELECT [HLA TAT].[HLA ID], [HLA TAT].[Last Name], [HLA TAT].[First Name], [HLA TAT].[Date Received]
FROM [HLA TAT]
WHERE ((([HLA TAT].[HLA ID])=[Date Received]) AND (([HLA TAT].[Date Received])=[WhatMonth]));

I'm not sure how to use this in export button on frmSample. Thanks.

 

[awake2424]

I get, The select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

Also, how to a format the button on the form to include the [What Month] user prompt? Thank you

Public Sub ExportQuery1ToExcel()
Const FILE_PATH As String = "C:\Users\cmccabe\Desktop\"
Dim FullPath As String
    strFullPath = FILE_PATH
    MsgBox ("Enter Month")
    DoCmd.TransferSpreadsheet acExport, , "TAT Query", strFullPath & "TAT.xlsx", False
    MsgBox ("Export is complete.")
End Sub