Running reports for a date range...

Karl,

Thanks for the tips, The code I have for the report button now is:
Private Sub EmpButton_Click()
On Error GoTo Err_EmpButton_Click

Dim stDocName As String

stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , [Date] =
Forms!FreqReportMenu!FromDate And [Date] = Forms!FreqReportMenu!ToDate

Exit_EmpButton_Click:
Exit Sub

Err_EmpButton_Click:
MsgBox Err.Description
Resume Exit_EmpButton_Click

End Sub

When I try to run this, I get the error:
cannot find the field '|' referred to the expression. Did I have something
marked off wrong? Thanks!

"KARL DEWEY" wrote:

Use those FromDate and ToDate controls as criteria in the query for the
report like this --
Between [Forms]![YourFormName]![FromDate] and
[Forms]![YourFormName]![ToDate]

--
Build a little, test a little.


"TheDrescher" wrote:

I'm trying to run reports on my database from the Records table for a
specific date range. When a record is saved, the date and time are included
as fields in the table. I've made a menu form to run the report where the
user can select FromDate and ToDate controls. When I run the report now, I
simply get all the records regardless of the date range selected. Is there a
specific way to code this? Thanks!

I've tried working the code and have the WHERE clause stating:
stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , Date =
Forms!FreqReportMenu![FromDate] And Date = Forms!FreqReportMenu![ToDate]

and now I just get an empty report. Any thoughts would be a great help.
Thanks!

"TheDrescher" wrote:

Karl,

Thanks for the tips, The code I have for the report button now is:
Private Sub EmpButton_Click()
On Error GoTo Err_EmpButton_Click

Dim stDocName As String

stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , [Date] =
Forms!FreqReportMenu!FromDate And [Date] = Forms!FreqReportMenu!ToDate

Exit_EmpButton_Click:
Exit Sub

Err_EmpButton_Click:
MsgBox Err.Description
Resume Exit_EmpButton_Click

End Sub

When I try to run this, I get the error:
cannot find the field '|' referred to the expression. Did I have something
marked off wrong? Thanks!

"KARL DEWEY" wrote:

Use those FromDate and ToDate controls as criteria in the query for the
report like this --
Between [Forms]![YourFormName]![FromDate] and
[Forms]![YourFormName]![ToDate]

--
Build a little, test a little.


"TheDrescher" wrote:

I'm trying to run reports on my database from the Records table for a
specific date range. When a record is saved, the date and time are included
as fields in the table. I've made a menu form to run the report where the
user can select FromDate and ToDate controls. When I run the report now, I
simply get all the records regardless of the date range selected. Is there a
specific way to code this? Thanks!

Try:
Dim strWhere as String
Dim stDocName as String
stDocName = "ByEmployee"
strWhere = "[Date] = #" & _
Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _
Forms!FreqReportMenu![ToDate] & "# "
DoCmd.OpenReport stDocName, acPreview, , strWhere

Date is a bad name for a field since Date is a function name. Also, if the
code is running in the form FreqReportMenu then you can change the strWhere
assignment to:
strWhere = "[Date] = #" & _
Me![FromDate] & "# And [Date] =#" & _
Me![ToDate] & "# "
or
strWhere = "[Date] Between #" & Me![FromDate] & "# And #" & Me![ToDate]
& "# "

--
Duane Hookom
MS Access MVP



"TheDrescher" wrote in message
...
I've tried working the code and have the WHERE clause stating:
stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , Date =
Forms!FreqReportMenu![FromDate] And Date = Forms!FreqReportMenu![ToDate]

and now I just get an empty report. Any thoughts would be a great help.
Thanks!

"TheDrescher" wrote:

Karl,

Thanks for the tips, The code I have for the report button now is:
Private Sub EmpButton_Click()
On Error GoTo Err_EmpButton_Click

Dim stDocName As String

stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , [Date] =
Forms!FreqReportMenu!FromDate And [Date] = Forms!FreqReportMenu!ToDate

Exit_EmpButton_Click:
Exit Sub

Err_EmpButton_Click:
MsgBox Err.Description
Resume Exit_EmpButton_Click

End Sub

When I try to run this, I get the error:
cannot find the field '|' referred to the expression. Did I have
something
marked off wrong? Thanks!

"KARL DEWEY" wrote:

Use those FromDate and ToDate controls as criteria in the query for the
report like this --
Between [Forms]![YourFormName]![FromDate] and
[Forms]![YourFormName]![ToDate]

--
Build a little, test a little.


"TheDrescher" wrote:

I'm trying to run reports on my database from the Records table for a
specific date range. When a record is saved, the date and time are
included
as fields in the table. I've made a menu form to run the report
where the
user can select FromDate and ToDate controls. When I run the report
now, I
simply get all the records regardless of the date range selected. Is
there a
specific way to code this? Thanks!

Thanks for the information Duane! I've adapted the code to work with what I
need. Per your suggestion I've changed the date field to something less
conflicting with functional code.

"Duane Hookom" wrote:

Try:
Dim strWhere as String
Dim stDocName as String
stDocName = "ByEmployee"
strWhere = "[Date] = #" & _
Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _
Forms!FreqReportMenu![ToDate] & "# "
DoCmd.OpenReport stDocName, acPreview, , strWhere

Date is a bad name for a field since Date is a function name. Also, if the
code is running in the form FreqReportMenu then you can change the strWhere
assignment to:
strWhere = "[Date] = #" & _
Me![FromDate] & "# And [Date] =#" & _
Me![ToDate] & "# "
or
strWhere = "[Date] Between #" & Me![FromDate] & "# And #" & Me![ToDate]
& "# "

--
Duane Hookom
MS Access MVP



"TheDrescher" wrote in message
...
I've tried working the code and have the WHERE clause stating:
stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , Date =
Forms!FreqReportMenu![FromDate] And Date = Forms!FreqReportMenu![ToDate]

and now I just get an empty report. Any thoughts would be a great help.
Thanks!

"TheDrescher" wrote:

Karl,

Thanks for the tips, The code I have for the report button now is:
Private Sub EmpButton_Click()
On Error GoTo Err_EmpButton_Click

Dim stDocName As String

stDocName = "ByEmployee"
DoCmd.OpenReport stDocName, acPreview, , [Date] =
Forms!FreqReportMenu!FromDate And [Date] = Forms!FreqReportMenu!ToDate

Exit_EmpButton_Click:
Exit Sub

Err_EmpButton_Click:
MsgBox Err.Description
Resume Exit_EmpButton_Click

End Sub

When I try to run this, I get the error:
cannot find the field '|' referred to the expression. Did I have
something
marked off wrong? Thanks!

"KARL DEWEY" wrote:

Use those FromDate and ToDate controls as criteria in the query for the
report like this --
Between [Forms]![YourFormName]![FromDate] and
[Forms]![YourFormName]![ToDate]

--
Build a little, test a little.


"TheDrescher" wrote:

I'm trying to run reports on my database from the Records table for a
specific date range. When a record is saved, the date and time are
included
as fields in the table. I've made a menu form to run the report
where the
user can select FromDate and ToDate controls. When I run the report
now, I
simply get all the records regardless of the date range selected. Is
there a
specific way to code this? Thanks!