Simple Date Range Querie

[luism]

Hello,

We can't figure out how to do this can someone please assist.

Have the following query:

SELECT Tbl_Contacts.LName, Tbl_Contacts.FName, Tbl_Contacts.Zip, Tbl_Contacts.Email, Tbl_Contacts.DateCreated
FROM Tbl_Contacts;

The query results in all contacts on the database, which helps but we need to filter it for another purpose.

We need either:

- New contacts added to the database during the last seven days (based on Tbl_Contacts.DateCreated) and have it exported as an excel spreadsheet file on the desktop
- The ability to create a small form that allows us to enter two dates and have the query return the contacts only between the two entered dates. (based on Tbl_Contacts.DateCreated) and have it exported as an excel spreadsheet file on the desktop

Any help would be greatly appreciated, thank you.

 

[pbaldy]

Where are you stuck? Simple enough to use the Build function in the query criteria to point to two form controls. Use Between, like

Between FirstFormReference And SecondFormReference

You can use TransferSpreadsheet or OutputTo to do the export.

 

[luism]

Ok here is what I came up with so far

My Query:
SELECT Tbl_Contacts.LName AS [Last Name], Tbl_Contacts.FName AS [First Name], Tbl_Contacts.Zip, Left(Tbl_Contacts.Email, InStr(Tbl_Contacts.Email, "#") - 1) AS Email, Tbl_Contacts.InitialContact AS [Contact Created]
FROM Tbl_Contacts
WHERE (((Tbl_Contacts.InitialContact) Between [Forms]![Frm_Contacts]![txtBeginDate] And [Forms]![Frm_Contacts]![txtEndDate]));

My Form:
Begin Date: [txtBeginDate]
End Date: [txtEndDate]
[Export Contacts] - Button with On Click Event Procedure

My VBA:
Private Sub Frm_ExportContacts_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry_ExportContacts", "ExportedContacts.xlsx", True
MsgBox "Contacts Were Exported to Your Documents Folder", vbOKOnly, "Contacts Exported!"
End Sub

I need help with the following
- Ignore any "append data" errors
- Ability to overwrite the .xlsx file that gets placed in "Documents" folder

Thank you for the guidance, pbaldy

 

[pbaldy]

Not sure you'd get any append errors with that, but you can try adding

DoCmd.SetWarnings False

before the existing code and

DoCmd.SetWarnings True

after the code. I think that will overwrite by default. Have you experienced otherwise?

 

[luism]

Perfect that worked.

It was giving an append data error because some contacts did not have an email on the table.

Last item we need please:
- Ability to overwrite the .xlsx file that gets placed in "Documents" folder by default.

Currently, it doesn't overwrite. If the file gets deleted then the new version shows up.

 

[pbaldy]

Perhaps I'm wrong. Try using DoCmd.OutputTo, which I know overwrites (just checked on a db that exports stuff nightly).

 

[luism]

Unfortunately that got complex for me, I will just instruct the users to delete the file before trying to export a new one. With the exception of overwrite it's working 100%, thanks!

 

[pbaldy]

You can use Kill before the export to delete the existing file. I can't recall if it will throw an error if the doesn't exist, but if it does it's easy to trap for.