Parameters in the query

[T. W.]

Hello there,

I have a Report that pulls from a Select Query, that pulls from two CrossTab
Queries, that both pull from the same Select Query, that pull from a set of
tables (I explain it better below).

The first Select Query pulls from a set of tables and is set to View Totals.

The two CrossTab Queries pull from the first Select Query.
One counts employees per department.
One sums the hours per department.

The final Select Query joins the two CrossTab queries together so the report
can more easily display the needed information.

This works quite well, but, we need it to prompt the user for the site.
There is a site field in the first Select Query, but using Parameters in the
query doesn't seem to provide the results.

Specifying the Parameter in the CrossTab queries causes you to be asked
twice.

I've seen in the newsgroup that putting it in a form would be best, but I'm
unlear how to do that since I have to have the report generated from the
final SelectQuery.

What's the best solution for this dilema?

 

[Duane Hookom]

There have been several posts lately that describe how to create a form with
a control that allows the user to either select or enter a site. Here are
some instructions http://www.fontstuff.com/access/acctut08.htm.

You must also declare the data types of any parameters in crosstab queries.
Again, this question has been answered many times in the past couple weeks.

If you can't find this information then reply back in this thread.

 

[T. W.]

I looked through the info on the link you sent.

I need the system to be able to launch a report that pulls from that query.
The below link discusses having a form used to prompt for cirteria for the
query, but the user needs to just be able to launch the report and be asked
for what the query needs.

I guess I'm trying to get the user to be asked for the criteria that the
query needs, from when they launch the report.

 

[Duane Hookom]

I believe all criteria should come from form controls. You can set up a
report's recordsource query to pull criteria values from a form and then
open the form when the report opens:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmDateRange", , , , , acDialog
If Not IsLoaded("frmDateRange") Then
Cancel = True
End If
End Sub

 

[T. W.]

When you say report's recordsource query, I assume you mean the query the
report pulls from?

....and I'm afraid I'm not familiar enough with the VBA workspace (or the
code below) to make what you're asking. Is there another way, or maybe I
just need more in-depth instructions. Sorry to be a hassle.

I understand (correct me if I'm wrong) that I can make a form that puts the
criteria into the query.
You're saying that I can make that form appear prior to the Report being
launched, so that the form prompts the user for the criteria for the query,
and then the Report will use the query with the criteria in it?

My understanding is that if the form passes the criteria to the query, its
only used for when the query is launched/running. The report would pull
from the saved query in the database, not the already running query in
memory; right?

 

[Duane Hookom]

I think you have the idea. The report's record source could have a criteria
like

SaleDate Between Forms!frmDateRange!txtFrom And Forms!frmDateRange!txtTo

Your form (frmDateRange) has two text boxes txtFrom and txtTo. You add the
code below to the On Open event of the report.

As the report opens, the form will appear and wait for the form to open and
allow the user to enter from and to dates. You would add a Command Button
on the frmDateRange with code like:
Private Sub Preview_Click()
If IsNull(Me.[txtFrom]) Or IsNull(Me.[txtTo]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "txtFrom"
Else
If [txtFrom] > [txtTo] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtFrom"
Else
Me.Visible = False
End If
End If
End Sub

 

[T. W.]

Duane,
Not to sound too stupid, and yes I know how to do several things in
Access, but I would need instructions on how to do the below. I'm not well
versed on using the VBA workspace. Please help.

I think you have the idea. The report's record source could have a criteria
like

SaleDate Between Forms!frmDateRange!txtFrom And Forms!frmDateRange!txtTo

Your form (frmDateRange) has two text boxes txtFrom and txtTo. You add the
code below to the On Open event of the report.

As the report opens, the form will appear and wait for the form to open
and allow the user to enter from and to dates. You would add a Command
Button on the frmDateRange with code like:
Private Sub Preview_Click()
If IsNull(Me.[txtFrom]) Or IsNull(Me.[txtTo]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "txtFrom"
Else
If [txtFrom] > [txtTo] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtFrom"
Else
Me.Visible = False
End If
End If
End Sub

--
Duane Hookom
MS Access MVP
--

When you say report's recordsource query, I assume you mean the query the
report pulls from?

...and I'm afraid I'm not familiar enough with the VBA workspace (or the
code below) to make what you're asking. Is there another way, or maybe I
just need more in-depth instructions. Sorry to be a hassle.

I understand (correct me if I'm wrong) that I can make a form that puts
the criteria into the query.
You're saying that I can make that form appear prior to the Report being
launched, so that the form prompts the user for the criteria for the
query, and then the Report will use the query with the criteria in it?

My understanding is that if the form passes the criteria to the query,
its only used for when the query is launched/running. The report would
pull from the saved query in the database, not the already running query
in memory; right?

 

[Duane Hookom]

Open the report in design view. View the properties for the report. Find the
Event property for On Open. Click the builder button [...] and select code
view. Enter the code to look something like:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmDateRange", , , , , acDialog
If Not IsLoaded("frmDateRange") Then
Cancel = True
End If
End Sub

Let us know how far you get and if you still have questions.

--
Duane Hookom
MS Access MVP

Duane,
Not to sound too stupid, and yes I know how to do several things in
Access, but I would need instructions on how to do the below. I'm not well
versed on using the VBA workspace. Please help.

I think you have the idea. The report's record source could have a criteria
like

SaleDate Between Forms!frmDateRange!txtFrom And Forms!frmDateRange!txtTo

Your form (frmDateRange) has two text boxes txtFrom and txtTo. You add the
code below to the On Open event of the report.

As the report opens, the form will appear and wait for the form to open
and allow the user to enter from and to dates. You would add a Command
Button on the frmDateRange with code like:
Private Sub Preview_Click()
If IsNull(Me.[txtFrom]) Or IsNull(Me.[txtTo]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "txtFrom"
Else
If [txtFrom] > [txtTo] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "txtFrom"
Else
Me.Visible = False
End If
End If
End Sub

--
Duane Hookom
MS Access MVP
--

When you say report's recordsource query, I assume you mean the query the
report pulls from?

...and I'm afraid I'm not familiar enough with the VBA workspace (or the
code below) to make what you're asking. Is there another way, or maybe I
just need more in-depth instructions. Sorry to be a hassle.

I understand (correct me if I'm wrong) that I can make a form that puts
the criteria into the query.
You're saying that I can make that form appear prior to the Report being
launched, so that the form prompts the user for the criteria for the
query, and then the Report will use the query with the criteria in it?

My understanding is that if the form passes the criteria to the query,
its only used for when the query is launched/running. The report would
pull from the saved query in the database, not the already running query
in memory; right?



I believe all criteria should come from form controls. You can set up a
report's recordsource query to pull criteria values from a form and then
open the form when the report opens:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmDateRange", , , , , acDialog
If Not IsLoaded("frmDateRange") Then
Cancel = True
End If
End Sub

--
Duane Hookom
MS Access MVP
--

"T. W." <T.W.> wrote in message
I looked through the info on the link you sent.

I need the system to be able to launch a report that pulls from that
query. The below link discusses having a form used to prompt for
cirteria for the query, but the user needs to just be able to launch
the report and be asked for what the query needs.

I guess I'm trying to get the user to be asked for the criteria that
the query needs, from when they launch the report.



There have been several posts lately that describe how to create a
form with a control that allows the user to either select or enter a
site. Here are some instructions
http://www.fontstuff.com/access/acctut08.htm.

You must also declare the data types of any parameters in crosstab
queries. Again, this question has been answered many times in the past
couple weeks.

If you can't find this information then reply back in this thread.

--
Duane Hookom
MS Access MVP
--

Hello there,

I have a Report that pulls from a Select Query, that pulls from two
CrossTab Queries, that both pull from the same Select Query, that
pull from a set of tables (I explain it better below).

The first Select Query pulls from a set of tables and is set to View
Totals.

The two CrossTab Queries pull from the first Select Query.
One counts employees per department.
One sums the hours per department.

The final Select Query joins the two CrossTab queries together so the
report can more easily display the needed information.

This works quite well, but, we need it to prompt the user for the
site. There is a site field in the first Select Query, but using
Parameters in the query doesn't seem to provide the results.

Specifying the Parameter in the CrossTab queries causes you to be
asked twice.

I've seen in the newsgroup that putting it in a form would be best,
but I'm unlear how to do that since I have to have the report
generated from the final SelectQuery.

What's the best solution for this dilema?