Passing Parameters to a Query

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""


M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
 
First,
When you use Between ... And ... The smallest (or earliest) value must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]

Next thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I would also suggest that you use a form with two text control for entering the
start and end date and a button to execute the code.

Example:
Form named: Dlg_GetDates
Control named: txtStartDate
Control named: txtEndDate
Button named: btnPrintReports

Modify your queries so the parameters are
Between Forms!Dlg_GetDates!txtStartDate and Forms!Dlg_GetDates!txtEndDate
or if you only need one of the dates, just reference that one.

The code for btnPrintReports would be something like

strDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview

If you are going to preview reports, you might want one button for each report.
You can only preview one report at a time. There are ways to preview a report
and then when the user closes the report, the next report will preview, but that
is complex and unless you want to really stretch your ability right away, you
are probably better off with the simpler solution of having a button for each report.

The DLG_GETDates form must remain open while the report(s) run, but it can be
closed after the report prints.

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""

M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
Click to expand...
 
Fantastic - Thank you. I'm working on it right now (and
probably into tomorrow). I'll post again if I get stuck.
Sara
-----Original Message-----
First,
When you use Between ... And ... The smallest (or earliest) value must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]

Next thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I would also suggest that you use a form with two text control for entering the
start and end date and a button to execute the code.

Example:
Form named: Dlg_GetDates
Control named: txtStartDate
Control named: txtEndDate
Button named: btnPrintReports

Modify your queries so the parameters are
Between Forms!Dlg_GetDates!txtStartDate and Forms! Dlg_GetDates!txtEndDate
or if you only need one of the dates, just reference that one.

The code for btnPrintReports would be something like

strDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview

If you are going to preview reports, you might want one button for each report.
You can only preview one report at a time. There are ways to preview a report
and then when the user closes the report, the next report will preview, but that
is complex and unless you want to really stretch your ability right away, you
are probably better off with the simpler solution of
Click to expand...
having a button for each report.
The DLG_GETDates form must remain open while the report (s) run, but it can be
closed after the report prints.

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""

M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
Click to expand...
.
Click to expand...
 
Two problems now:
1. The reports run in test, individuall, but I don't
think I'm getting the date from the screen into the module
(code below)

2. The reports all show the date entered, and date range
where calculated, in the title. I use "Between " &
DateAdd... But the reports (when I run in test) all show
#Name. Something easy, I'm sure, but I've spent a few
hours on this and not finding any solution.

3. I am trying to setfocus on the date field right away,
then check to see if it is empty before running the
module. I am not sure I did that right. I didn't bother
to clear the date after running the reports. Should I?
How?

Thanks (again).

Code:
Private Sub cmdClose_Click()
DoCmd.Close
End Sub


Private Sub cmdPrintPreview_Click()


Dim stDocName As String 'Report as Variable
to print
Dim strStepErrorMsg As String 'Description for
Error


' Find out what button was chosen, then print preview
all reports for that choice

If (Me!grpReportList = 1) Then ' Daily Reports
stDocName = "R_Daily Over Short-Not 0"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS Details over $5 within 90
days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview

End If

If (Me!grpReportList = 2) Then ' Weekly Reports

End If

If (Me!grpReportList = 3) Then ' Monthly Reports

End If

If (Me!grpReportList = 4) Then ' Quarterly
Reports

End If


Me.getSalesDate.SetFocus

PrintOSReports_Exit:
Exit Sub

PrintOSReports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume PrintOSReports_Exit
End Sub

Private Sub Form_Open(Cancel As Integer)
Me![getSalesDate].SetFocus
End Sub


-----Original Message-----
First,
When you use Between ... And ... The smallest (or earliest) value must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]

Next thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I would also suggest that you use a form with two text control for entering the
start and end date and a button to execute the code.

Example:
Form named: Dlg_GetDates
Control named: txtStartDate
Control named: txtEndDate
Button named: btnPrintReports

Modify your queries so the parameters are
Between Forms!Dlg_GetDates!txtStartDate and Forms! Dlg_GetDates!txtEndDate
or if you only need one of the dates, just reference that one.

The code for btnPrintReports would be something like

strDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview

If you are going to preview reports, you might want one button for each report.
You can only preview one report at a time. There are ways to preview a report
and then when the user closes the report, the next report will preview, but that
is complex and unless you want to really stretch your ability right away, you
are probably better off with the simpler solution of
Click to expand...
having a button for each report.
The DLG_GETDates form must remain open while the report (s) run, but it can be
closed after the report prints.

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""

M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
Click to expand...
.
Click to expand...
 
John said:
First,
When you use Between ... And ... The smallest (or earliest) value
must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]
Click to expand...

Not on my machine. The former works just fine.
 
I figured it out - amazing what a few hours of sleep will
do! I had "SaleSdate" in some places, and "Saledate" in
others. All are the same now and I have data! Thanks so
much. In the holiday spirit (no party till tonite), On to
my next battle.
Sara
-----Original Message-----
Two problems now:
1. The reports run in test, individuall, but I don't
think I'm getting the date from the screen into the module
(code below)

2. The reports all show the date entered, and date range
where calculated, in the title. I use "Between " &
DateAdd... But the reports (when I run in test) all show
#Name. Something easy, I'm sure, but I've spent a few
hours on this and not finding any solution.

3. I am trying to setfocus on the date field right away,
then check to see if it is empty before running the
module. I am not sure I did that right. I didn't bother
to clear the date after running the reports. Should I?
How?

Thanks (again).

Code:
Private Sub cmdClose_Click()
DoCmd.Close
End Sub


Private Sub cmdPrintPreview_Click()


Dim stDocName As String 'Report as Variable
to print
Dim strStepErrorMsg As String 'Description for
Error


' Find out what button was chosen, then print preview
all reports for that choice

If (Me!grpReportList = 1) Then ' Daily Reports
stDocName = "R_Daily Over Short-Not 0"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS Details over $5 within 90
days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview

End If

If (Me!grpReportList = 2) Then ' Weekly Reports

End If

If (Me!grpReportList = 3) Then ' Monthly Reports

End If

If (Me!grpReportList = 4) Then ' Quarterly
Reports

End If


Me.getSalesDate.SetFocus

PrintOSReports_Exit:
Exit Sub

PrintOSReports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume PrintOSReports_Exit
End Sub

Private Sub Form_Open(Cancel As Integer)
Me![getSalesDate].SetFocus
End Sub


-----Original Message-----
First,
When you use Between ... And ... The smallest (or earliest) value must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter
Click to expand...
Sale
Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]

Next thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I would also suggest that you use a form with two text control for entering the
start and end date and a button to execute the code.

Example:
Form named: Dlg_GetDates
Control named: txtStartDate
Control named: txtEndDate
Button named: btnPrintReports

Modify your queries so the parameters are
Between Forms!Dlg_GetDates!txtStartDate and Forms! Dlg_GetDates!txtEndDate
or if you only need one of the dates, just reference
Click to expand...
that
one.

The code for btnPrintReports would be something like

strDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview

If you are going to preview reports, you might want one button for each report.
You can only preview one report at a time. There are ways to preview a report
and then when the user closes the report, the next
Click to expand...
report
will preview, but that
is complex and unless you want to really stretch your ability right away, you
are probably better off with the simpler solution of
Click to expand...
having a button for each report.
The DLG_GETDates form must remain open while the report (s) run, but it can be
closed after the report prints.

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""

M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
Click to expand...
.
Click to expand...
.
Click to expand...
 
Great. Glad you worked thru it. And the good part of solving it yourself is
that you will remember the solution a lot easier than if if was handed to you.
I figured it out - amazing what a few hours of sleep will
do! I had "SaleSdate" in some places, and "Saledate" in
others. All are the same now and I have data! Thanks so
much. In the holiday spirit (no party till tonite), On to
my next battle.
Sara
-----Original Message-----
Two problems now:
1. The reports run in test, individuall, but I don't
think I'm getting the date from the screen into the module
(code below)

2. The reports all show the date entered, and date range
where calculated, in the title. I use "Between " &
DateAdd... But the reports (when I run in test) all show
#Name. Something easy, I'm sure, but I've spent a few
hours on this and not finding any solution.

3. I am trying to setfocus on the date field right away,
then check to see if it is empty before running the
module. I am not sure I did that right. I didn't bother
to clear the date after running the reports. Should I?
How?

Thanks (again).

Code:
Private Sub cmdClose_Click()
DoCmd.Close
End Sub


Private Sub cmdPrintPreview_Click()


Dim stDocName As String 'Report as Variable
to print
Dim strStepErrorMsg As String 'Description for
Error


' Find out what button was chosen, then print preview
all reports for that choice

If (Me!grpReportList = 1) Then ' Daily Reports
stDocName = "R_Daily Over Short-Not 0"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS Details over $5 within 90
days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview

stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a
problem with Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview

End If

If (Me!grpReportList = 2) Then ' Weekly Reports

End If

If (Me!grpReportList = 3) Then ' Monthly Reports

End If

If (Me!grpReportList = 4) Then ' Quarterly
Reports

End If


Me.getSalesDate.SetFocus

PrintOSReports_Exit:
Exit Sub

PrintOSReports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume PrintOSReports_Exit
End Sub

Private Sub Form_Open(Cancel As Integer)
Me![getSalesDate].SetFocus
End Sub


-----Original Message-----
First,
When you use Between ... And ... The smallest (or earliest) value must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter
Click to expand...
Sale
Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]

Next thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I would also suggest that you use a form with two text control for entering the
start and end date and a button to execute the code.

Example:
Form named: Dlg_GetDates
Control named: txtStartDate
Control named: txtEndDate
Button named: btnPrintReports

Modify your queries so the parameters are
Between Forms!Dlg_GetDates!txtStartDate and Forms! Dlg_GetDates!txtEndDate
or if you only need one of the dates, just reference
Click to expand...
that
one.

The code for btnPrintReports would be something like

strDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview

If you are going to preview reports, you might want one button for each report.
You can only preview one report at a time. There are ways to preview a report
and then when the user closes the report, the next
Click to expand...
report
will preview, but that
is complex and unless you want to really stretch your ability right away, you
are probably better off with the simpler solution of
Click to expand...
having a button for each report.
The DLG_GETDates form must remain open while the report (s) run, but it can be
closed after the report prints.


sara wrote:

I am new to all this - just completed a 2-day class and
having some trouble.

I have a dozen reports that my users have to create each
week (on Monday). The underlying queries all use
parameters to determine the date for data to use in each
report.

Some reports are based on more complicated queries, like
Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale
Date]), where the same date - Sale Date - is used as in
all the other reports.

I can't get the module to work with the:
Basic queries unless I take the parameter out completely
Complicated queries (like above) at all.

I have crosstabs to run, etc., and the users have to type
in the same date about 20-30 times!

Module below (if this helps)
'------------------------------------------------------
' M_Weekly_Reports
'----------------------------------------------------
Function M__Weekly_Reports()
On Error GoTo M__Weekly_Reports_Err

'Declare Variables

Dim dtReportDate As Date
Dim stDocName As String
Dim strStepErrorMsg As String

dtReportDate = InputBox("Enter Sales Date for
Reports", "Report Date Dialog Box")
stDocName = "R_Daily Over Short-Not 0"

' Daily Not TTP - enter Sales Date
strStepErrorMsg = "Tell IT there was a problem with
Daily OS not 0"
DoCmd.OpenReport stDocName, acPreview, , _
"""[Enter Sale Date] = dtReportDate"""

' 2ND REPORT Daily - more than 3 OS in 90 days - LPD
(enter Ending date - use Sales Date)
stDocName = "R_OS Details over $5 within 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 3 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 3RD REPORT Daily - warning 2 OS in 90 days - Support
(Enter ending date - Use Sales Date)
stDocName = "R_OS WARNING - 2 in 90 days"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Warning 2 in 90 days"
DoCmd.OpenReport stDocName, acPreview, "", ""

' 4TH REPORT Daily - Over Short over $50 (enter Sales
Date - pulls for the prior 7 days)
stDocName = "R_OverShort Over 50"
strStepErrorMsg = "Tell IT there was a problem with
Daily OS Over 50"
DoCmd.OpenReport stDocName, acPreview, "", ""

M__Weekly_Reports_Exit:
Exit Function

M__Weekly_Reports_Err:

MsgBox strStepErrorMsg
MsgBox Err.Description

Resume M__Weekly_Reports_Exit

End Function

Thanks - I need to get this automated before I go on
vacation (Though I'm on my way to crazy first, I think!)
Sara
.
Click to expand...
.
Click to expand...
Click to expand...
 
Well, I'll be dogged. It does work. I wonder why I thought it doesn't?
Perhaps I'd seen the behavior in some other database engine? Oh well, I'll
still do it my way, just in case. Stubborn ol' cuss - yep, I am that at times.

Joan said:
First,
When you use Between ... And ... The smallest (or earliest) value
must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]
Click to expand...

Not on my machine. The former works just fine.
Click to expand...
 
John said:
Well, I'll be dogged. It does work. I wonder why I thought it
doesn't? Perhaps I'd seen the behavior in some other database engine?
Oh well, I'll still do it my way, just in case. Stubborn ol' cuss -
yep, I am that at times.
Click to expand...

<g> That's 2 things you learned today (well yesterday).
 
I am doing pretty well, with the exception of the Crosstab
queries running from my form.

I took a simple one - first a query selects the data,
using the date fed in from the form ([Forms]!
[F_PrintSalesReports]![getWkMoDate])
and then that Query is input for the Crosstab.
The parameter is defined in both queries.

When I run the crosstab from the Query menu, it's fine.
When I run the crosstab from the Form, I get nothing.

Form code:
' --------------------------------------------------------
' OPTION 3 - MONTHLY SELECTED - Tax Query Only
' --------------------------------------------------------

If (Me!grpReportList = 3) Then
' Monthly Query for Report
' Set Field to allow date input - simply Visible date field
EnableWkMoButtons
' Sales Taxes Query
DoCmd.OpenQuery "Q: SalesbyCorp_Tax_NonTax_CT-
MONTH", acNormal, acReadOnly
End If

Much appreciate the help.

And, an FYI: I have a daily section that runs 9 reports.
They all open and sit in the tray, in preview mode. I
want the users to look at each one, print or snap
(whatever they're supposed to do) and close the preview.
This alone saves them so much time (not having to choose
the report and enter the parameter for each one). It also
guarantees they use the same date on all reports.

Sara
 
In Access the order of the larger and smaller values does not matter but in most (all?) other sql it does.

John Spencer (MVP) said:
Well, I'll be dogged. It does work. I wonder why I thought it doesn't?
Perhaps I'd seen the behavior in some other database engine? Oh well, I'll
still do it my way, just in case. Stubborn ol' cuss - yep, I am that at times.

Joan said:
First,
When you use Between ... And ... The smallest (or earliest) value
must appear
first. So

Between [Enter Sale Date] And DateAdd("d",-90,[Enter Sale Date])

Must be rewritten to

Between DateAdd("d",-90,[Enter Sale Date]) And [Enter Sale Date]
Click to expand...

Not on my machine. The former works just fine.
Click to expand...
Click to expand...
 
Back
Top