ms access select using vba

Question

Hi,

I have form called as frmReports

Two text boxes Name called as txtdatefrom, txtDateTo

Table called as Table1

I need a vba code or query to a command button,

on click of a command button the script should export the Table1 data in excel format according to date typed in txtdatefrom, txtDateTo

something like below

dim sql as string

sql = "select * from Table1 where (Date between  txtdatefrom  and  txtDateTo)

Answer 1

I have form called as frmReports

 

Two text boxes Name called as txtdatefrom, txtDateTo

 

Table called as Table1

 

I need a vba code or query to a command button,

 

on click of a command button the script should export the Table1 data in excel format according to date typed in txtdatefrom, txtDateTo

 

something like below

 

dim sql as string

 

sql = "select * from Table1 where (Date between  txtdatefrom  and  txtDateTo)

I would approach it like this:

First I'd create a stored query that refers to the controls on the form for criteria.  For example, you might create a query named "qryExport" with this SQL:

    SELECT * FROM Table1 WHERE Table1.Date Between Forms!frmReports!txtDateFrom  And  Forms!frmReports!txtDateTo

Then have in the Click event procedure for your command button, have the following line of code:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", CurrentProject.Path & "\ExportedData.xls", True

You may well want to modify "ExportedData.xls" to some other, more meaningful name, and you may want to change the folder path where it is stored to something besides CurrentProject.Path (which is the folder containing the database).

It's also possible that you want to allow for the possibility that the start date or the end date on the form may be blank.  In that case, the query SQL must be different.  Let me know and I'll give you a modified SQL statement.