If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Running reports for a date range...
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! |
#3
|
|||
|
|||
Running reports for a date range...
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! |
#4
|
|||
|
|||
Running reports for a date range...
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! |
Thread Tools | |
Display Modes | |
|
|