Query Start Date and End Date

[netrusher]

I am using the below code to transfer four queries to an excel file. Everything works just fine. My question is as follows: Each of the query's that I am using has as part of the critiera Where [TodaysDate] Between [Start Date] And [End Date]; When I use the command button to transfer the queries I have to input the Start date and the End Date 4 times. Is there another way to accomplish this. When the queries are used individually I need the Start Date and End Date critieria. When I transfer all 4 queries at the same time each query will always have the same Start Date and End Date. When I hit the command button to transfer all four queries I would like to only enter the Start Date and End Date 1 time. Any suggestions? and thanks.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AllFaultsQry", "\\abmfl2sv\shared\Everyone\abajacks\BereaWUFaults\BereaWUFaults.xls", True, "All Faults"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FaultsQry", "\\abmfl2sv\shared\Everyone\abajacks\BereaWUFaults\BereaWUFaults.xls", True, "Faults"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SystemGroupTotalsMechanicalQry", "\\abmfl2sv\shared\Everyone\abajacks\BereaWUFaults\BereaWUFaults.xls", True, "Mechanical Faults"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SystemGroupTotalsCosmeticQry", "\\abmfl2sv\shared\Everyone\abajacks\BereaWUFaults\BereaWUFaults.xls", True, "Cosmetic Faults"

 

[JerryKlmns]

Add 2 text boxes to enter valid start & end dates, modify your queries to filter (WHERE clause) values of the date field between those dates on your form

Something like

WHERE DateField Between [Forms]![frmStats]![txtFrom] And [forms]![frmStats]![txtTo]));

 

[netrusher]

I have created a Form with two calendars on the Form.
CalendarStart and CalendarEnd. Can someone tell me how I can have the queries and/or Command Button to open this Form, allow the person to select the Start Date and End Date from the two Calendars?

Thanks

 

[JerryKlmns]

1.
Open the form

DoCmd.OpenForm "YourFormName"

2.
Place 2 text boxes (txtStart & txtEnd). Your calendar control names (ActiveXCtl0 & ActiveXCtl1)

On the properties of the form, select Event tab click on "On Click" and double click the 3 dots. Select Code Builder and place these subs (copy paste)

Private Sub ActiveXCtl0_AfterUpdate()
    txtStart.SetFocus
    txtStart.Text = ActiveXCtl0.Value
End Sub

Private Sub ActiveXCtl1_AfterUpdate()
    txtEnd.SetFocus
    txtEnd.Text = ActiveXCtl1.Value
End Sub

3.
Modify your query adding this (in SQL view)

WHERE DateField Between [Forms]![YourFormName]![txtStart] And [Forms]![YourFormName]![txtEnd]));

4.
Execute the query.
Add a button on your form and on the wizard just click Finish. Select the button, right click & select properties. On the event tab click the [Event Procedure] of the "On Click". You probably see something like

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click


    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

which you just replace the following 2 lines
Screen.PreviousControl.SetFocus
DoCmd.FindNext
with this one
DoCmd.OpenQuery "YourQueryName"

And you are all set!