AC2K Output query to Excel with date critera entered on form

[nowanda]

Howdy!

I can print preview my report using date criteria from a form (with user entry) but I don't see how I can do the same thing when I output to Excel. Can anyone suggest another method of action? (Feel free to criticize the way I coded this too - it works - but is not pretty!)

To Print Preview (this works)
Form has a frame (Frame) with 0 = all the data to be returned; -1 for the date selection based on the Microsoft Date and Time picker (From & To)

Sub Preview_Click()
If Me.Frame = 1 Then
' *** Handle date (from and to) entry / where clause ***
If Me.From.Value > Me.To.Value Then
MsgBox ("From date must be before To date")
Exit Sub
End If

Between = "date Between #" & Me.From & "# And #" & Me.To & "#"
DoCmd.OpenReport "Report", acPreview, , Between

Else
If Me.Frame = 0 Then
DoCmd.OpenReport "Report", acPreview
End If
End If
End Sub

To Export to Exccel (this needs help)
Form has the same frame (Frame) with 0 = all the data to be returned; -1 for the date selection based on the Microsoft Date and Time picker (From & To). This code does not include any date criteria from the form at all... please help me add it somehow!

Sub Export_Excel_Click()
DoCmd.OutputTo acOutputQuery, "Report_Query", acFormatXLS, "C:\Data\Report.xls"
End Sub

Suggestions?
Nowanda

 

[bat17]

Easiest option is probably to just have two queries, one with date criteria linked to your form and the other without date criteria. Then just run whichever is appropriate.

HTH

Peter

 

[nowanda]

Re: AC2K Output query to Excel with date critera entered on

Oops!

The issue is specifically that I want the date criteria to be accessed for the output to excel as the print preview does...

When you want to print preview you can use a where clause to specify anything you would like:

DoCmd.OpenReport "Report", acPreview, , ¨¨where clause¨¨

But the OutputTo doesn't allow you to specify any criteria....
DoCmd.OutputTo acOutputQuery, "Report_QRY", acFormatXLS, "C:\Data\Report.xls"

Does that make more sense? I'm looking to add a where clause to my query which I am outputting to excel.

Nowanda

 

[bat17]

Assuming that Me.From and Me.To are fields on your form there is no need to create a where clause as the query can be linked to these fields directly. So have one query that will be linked to these fields for its criteria and another that will show all data. Then just use the appropriate query in your if statement.

Peter