Export Query - Different Fields

[luism]

Hello,

I'm trying to allow users to be able to make a query based on custom/different fields. Here is what I have

----

FORM
Begin Date (label): [txtBeginDate] (unbound text field)
End Date (label): [txtEndDate] (unbound text field)
[Export Contacts] (submit button with event procedure)


VB
'Export to Spreadsheet Based on Dates Selected Under "Export Contacts"
Private Sub Frm_ExportContacts_Click()
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry_ExportContacts", "ExportedContacts.xlsx", True
DoCmd.SetWarnings True
MsgBox "Contacts Were Exported to Your Documents Folder", vbOKOnly, "Contacts Exported!"
End Sub


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]));

----

Here's what I'd like to do

FORM
Begin Date (label): [txtBeginDate] (unbound text field)
End Date (label): [txtEndDate] (unbound text field)
Zip Code (label): [txtZip] (unbound text field)
Service Contract (label): [Check_SC] (unbound check box)
[Export Contacts] (submit button with event procedure)

Service Contract (tbl_ServiceContract) is a table linked to tbl_contacts with one to many relationship.

I'd like to make a query where the user

- Selects a begin date, selects an end date - if this is the only thing the user selects then export ALL contacts on the database

- Selects a begin date, selects an end date, types a zip code - exports as needed filtering all contacts with begin/end date AND zip code typed

- Selects a begin date, selects an end date, checks on the "service contract" check box - exports as needed filtering all contacts with begin/end date AND service contract data only

- Selects a begin date, selects an end date, types a zip cod, checks on the "service contract" check box - exports as needed filtering all contacts with begin/end date AND zip code typed AND service contract data only

Any help would be greatly appreciated!

 

[Ranman256]

If you need complex form filters THEN make a query from it, do this:
set any check boxes too.

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = ""Qry_ExportContacts"      'here is the query we use ...in the report too


    'the query is built depending on the various filters the user picks...

If Not IsNull(txtBeginDate) Then sWhere = sWhere & " and [Date] between #" & txtBeginDate & " and #" & txtEndDate & "#"
If Not IsNull(txtZip) Then sWhere = sWhere & " and [ZIP]='" & txtZip & "'"


    'remove 1st 'AND'
sWhere = Mid(sWhere, 4)

  
    'BUILD the query from the 'where'   *THIS MUST ALREADY EXIST in order to edit
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany"
If sWhere <> "" Then qdf.Sql = qdf.Sql & " WHERE " & sWhere
qdf.Close


    'open the query or report here!
'DOCMD.openquery kQRY
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, kQRY, "ExportedContacts.xlsx", True

SET qdf = nothing
End Sub

 

[luism]

alright great, thank you!