Automatic 1 Week Query/Report?

D

dohernan

I have a Query tied to a Report that tallies up forms done for the week.

If the Query is run Tuesday afternoon it should include forms dated that
very day, and go back to include forms from the previous Wednesday.
Example Wednesday 9/16 - Tuesday 9/22, inclusive.

If the query is run on Wednesday morning then it should not include forms
run that day, so 9/16- 9/22, ignoring 9/23 stuff.

Is there a way to set this up so I don't have to actually input the dates
every time I run the report?

Thanks
 
D

Duane Hookom

I would use a form with start and end dates entered into text boxes. This
will provide a nice interface that allows changes to the range but is very
convenient for the user.

You can set the default values of these text boxes to something like:

Name: txtStart
Default Value:=IIf(Time()<0.5,Date()-1,Date())-6

Name: txtEnd
Default Value: =IIf(Time()<0.5,Date()-1,Date())

Then, use the values of the controls to filter the report.
 
D

dohernan

Can you add this to the Report / Query itself? Ask for a date range before
running?

I don't know how to tie a Form into the other stuff, or how to use the info
you placed below.

Thanks.
 
D

Duane Hookom

Let's assume since this is a reports news group, you want to open a report
named "rptMyReportName" that displays records from a date range and the date
field in the report's record source is "MyDateField".

You would start with a form with the two text boxes I have suggested. Then
use the command button wizard to create a command button to open your report.
Then modify the code generated by the wizard to look like:

Dim stDocName As String
' add the next 10 lines and add ", , strWhere" to the DoCmd.OpenReport
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND MyDateField >=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND MyDateField <=#" & _
Me.txtEnd & "# "
End If
stDocName = "rptMyReportName"
DoCmd.OpenReport stDocName, acPreview, , strWhere
 
D

dohernan

Thanks, when I just do the text boxes (StartQueryDate, EndQueryDate) and the
Original "Open Report" Command button, everything works, the dates show, the
original report opens.
When I try to get it to ask me for dates, I get errors

I get the feeling I did a mistake somewhere.
The date pulled from the database is the form "Completed" one.

I created a Report called "HowManyofEachFormCompleted"

++++++++

Option Compare Database

Private Sub PreviewReportwDates_Click()
On Error GoTo Err_PreviewReportwDates_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartQueryDate) Then
strWhere = strWhere & " AND Completed >=#" & _
Me.StartQueryDate & "# "
End If
If Not IsNull(Me.EndQueryDate) Then
strWhere = strWhere & " AND Completed <=#" & _
Me.EndQueryDate & "# "
End If

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_PreviewReportwDates_Click:
Exit Sub

Err_PreviewReportwDates_Click:
MsgBox Err.Description
Resume Exit_PreviewReportwDates_Click

End Sub
 
D

Duane Hookom

I'm not sure what you mean by "When i try to get it to ask me for dates". I'm
not sure what your errors are.

Can we assume you have a date field in your report's record source named
"Completed"?

Are you entering dates into your form controls?
What values are displayed in your controls?
Is your locale date format M/D/Y?
What errors do you get when you click "PreviewReportsDates"?
 
D

dohernan

Yes, there's one called "Completed"
I was using the Default Dates in the Form controls, they show up correctly
I'm entering dates MM/DD/Year

Now I click "Preview" Report"
and nothing happens.
There are no Errors showing.

Thanks. :)



_________
Option Compare Database

Private Sub HowManyofEachFormCompleted_Click()
On Error GoTo Err_HowManyofEachFormCompleted_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartQueryDate) Then
strWhere = strWhere & " AND Completed >=#" & _
Me.StartQueryDate & "# "
End If
If Not IsNull(Me.EndQueryDate) Then
strWhere = strWhere & " AND Completed <=#" & _
Me.EndQueryDate & "# "
End If

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_StatReportbyDates_Click:
Exit Sub

Err_StatReportbyDates_Click:
MsgBox Err.Description
Resume Exit_StatReportbyDates_Click

End Sub

Private Sub OpeningReport_Click()

End Sub
 
D

Duane Hookom

Learn how to trouble-shoot. Try put a break point in the code probably the
line:
strWhere = " 1= 1 "
Then use F8 to step through the code to see what is happening. You can hover
your mouse pointer over variables or object references to see their values.
 
D

dohernan

I'm using Access 2003, and now the Command Button is opening up the Report
again, which is great. :)

However I just realized that the Report and Query have actual dates in them,
and the 2 dates entered in the Form don't Do anything.

I need to look into how to get the dates to work with the query, and how to
Capture the Dates that are entered and Post them in the Report Header, for
example

"Stats 9/30/2009 - 10/6/2009" If those are the 2 dates the person enters.

Thanks again. :)



++++++++++++++

Option Compare Database

Private Sub HowManyofEachFormCompleted_Click()
On Error GoTo Err_HowManyofEachFormCompleted_Click

Dim stDocName As String
DoCmd.OpenReport
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartQueryDate) Then
strWhere = strWhere & " AND Completed >=#" & _
Me.StartQueryDate & "# "
End If
If Not IsNull(Me.EndQueryDate) Then
strWhere = strWhere & " AND Completed <=#" & _
Me.EndQueryDate & "# "
Dim strWhere As String
strWhere = " 1=1 "
If Not IsNull(Me.StartQueryDate) Then
strWhere = strWhere & " AND Completed >=#" & _
Me.StartQueryDate & "# "
End If
If Not IsNull(Me.EndQueryDate) Then
strWhere = strWhere & " AND Completed <=#" & _
Me.EndQueryDate & "# "
End If

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_StatReportbyDatesForm_Click:
Exit Sub

Err_StatReportbyDatesForm_Click:
MsgBox Err.Description
Resume Exit_StatReportbyDates_Click

End Sub

Private Sub OpeningReport_Click()

End Sub
Private Sub PreviewButton_Click()
On Error GoTo Err_PreviewButton_Click

Dim stDocName As String

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewButton_Click:
Exit Sub

Err_PreviewButton_Click:
MsgBox Err.Description
Resume Exit_PreviewButton_Click

End Sub
+++++++++++
 
D

Duane Hookom

You seem to have three separate subs with three DoCmd.OpenReport. You code as
it is won't compile. You should add the line:
Option Explicit
immediately after the Option Compare Database line.

You should revisit my second posting in this thread.
 
D

Duane Hookom

Clearly, the Completed field is not in the report's record source. You can't
use a where condition in DoCmd.OpenReport referencing a field that isn't in
the report's record source.

Typically you will reference the controls on your form in the query.
Assuming your form name is frmDates, try:

SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!frmDates!StartQueryDate And
Forms!frmDates!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL
SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!frmDates!StartQueryDate And
Forms!frmDates!EndQueryDate
GROUP BY tblOtherForms.[Form Type];
 
D

dohernan

Thanks so much, it's exciting- I'm starting to see all the possibilities. :)

The Query itself now runs great, it asks for the form dates, and when I put
dates in so far it's been accurate. :)

However now my Form shows up blank, but if I look in Design View the 2 text
fields and command button are there. o.O

Once I get that running again(hopefully), how how do I Capture the Dates
that are entered and Post them in the Report Header? :)

I feel quite determined. ^^
 
D

dohernan

My Query so Far, I wonder if I should get rid of the word Form in the
StatReportByDatesForm
Does that cause any issues?

Thanks

++++++++

SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!StatReportByDatesForm!StartQueryDate
And
Forms!StatReportByDatesForm!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!StatReportByDatesForm!StartQueryDate
And
Forms!StatReportByDatesForm!EndQueryDate
GROUP BY tblOtherForms.[Form Type];
++++++++
 
D

Duane Hookom

Typically you want to have the form open and enter the dates into the
controls. Then click a button that opens the report. The Forms!.... just
needs to be the appropriate name of your form and the name of the control on
the form. The form must be open.
 
D

dohernan

All i changed was the SQL in the Query but now the Form opens blank, no
dates, no button. :/
 
D

Duane Hookom

I'm not sure how the form could open blank. Is your form bound to a record
source? Do you see the controls in design view?
 
D

dohernan

In design view you can see the 2 text boxes and the button.

But when you do Form View, there's nothing.
The text boxes are Unbound, with the default dates set in.
The Form is bound to a Query, and the Query looks to the form for dates.
The button code is below-

Option Compare Database

Private Sub HowManyofEachFormCompleted_Click()
On Error GoTo Err_HowManyofEachFormCompleted_Click

Dim stDocName As String
Dim strWhere As String
strWhere = " 1=1 "
If Not IsNull(Me.StartQueryDate) Then
strWhere = strWhere & " AND Completed >=#" & _
Me.StartQueryDate & "# "
End If
If Not IsNull(Me.EndQueryDate) Then
strWhere = strWhere & " AND Completed <=#" & _
Me.EndQueryDate & "# "
End If

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_StatReportbyDatesForm_Click:
Exit Sub

Err_StatReportbyDatesForm_Click:
MsgBox Err.Description
Resume Exit_StatReportbyDates_Click

End Sub

Private Sub OpeningReport_Click()

End Sub
Private Sub PreviewButton_Click()
On Error GoTo Err_PreviewButton_Click

Dim stDocName As String

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewButton_Click:
Exit Sub

Err_PreviewButton_Click:
MsgBox Err.Description
Resume Exit_PreviewButton_Click

End Sub


Private Sub PreviewButton_Enter()

End Sub


+++++++++

The Query SQL -

SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!StatReportByDatesForm!StartQueryDate
And
Forms!StatReportByDatesForm!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!StatReportByDatesForm!StartQueryDate
And
Forms!StatReportByDatesForm!EndQueryDate
GROUP BY tblOtherForms.[Form Type];


Thanks
 
D

Duane Hookom

Why is the form bound? If you aren't seeing the controls it's generally
caused by the form's record source not returning any records while the record
source is not updateable.

You have three On_Click and one _Enter event in your module. I suggested
earlier that you should type in
Option Explicit
in the General Declarations section under Option Compare Database

The first On_Click won't work because you don't have the [Completed] field
in the fields list of your report.

The second On_Click has no code so it can be deleted.

The third On Click should work
 
D

dohernan

Good morning.

I don't see how the Form is bound? The text boxes are Unbound.
I left the button with just the 3rd Onclick and the form is still blank.

The Report doesn't have a Completed Field, but the Query it runs off of
does. I'm not sure then how to marry this all.

The Button Now-
Option Compare Database
Option Explicit

Private Sub PreviewButton_Click()
On Error GoTo Err_PreviewButton_Click

Dim stDocName As String

stDocName = "HowManyofEachFormCompleted"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewButton_Click:
Exit Sub

Err_PreviewButton_Click:
MsgBox Err.Description
Resume Exit_PreviewButton_Click

End Sub


Private Sub PreviewButton_Enter()

End Sub



Thanks
 
D

Duane Hookom

Dohernan,
You stated "The Form is bound to a Query" and now you state "I don't see how
the Form is bound?" Huh?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif Function?? 3
Report Query 12
How can I create a calendar report in access 8
Dates not sorting correctly in Report 9
Date selection query 2
Count Week Of Month 4
Query from day of week 3
Report totals by day of week - from crosstab report 10

Top