Cross-tab query parameters

  • Thread starter Thread starter djhsmith23
  • Start date Start date
D

djhsmith23

How can I query for parameters in a cross-tab query? I need for my users to
be able to put in a date selection but the jet engine doesn't recognize the
request? Any help would be appreciated.

Thanks,
Donna
 
That's the problem. I don't want to declare specific parameters. How would I
use (Between [Type the beginning date] And [Type the ending date])? Access
won't accept it in the parameter's grid.
 
Declaring the parameters is not the same as specifying the values. You MUST
declare your parameters ([Type the beginning date], [Type the ending date])
in a crosstab. Open the query in design view. Choose Parameters from the
query menu. On the first line in the Parameters dialog box, type: [Type the
beginning date] and select Date/Time as the data type. Do the same thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
That's the problem. I don't want to declare specific parameters. How would I
use (Between [Type the beginning date] And [Type the ending date])? Access
won't accept it in the parameter's grid.

John Viescas said:
Donna-

You should always explicitly declare parameters in any query, but in a
Crosstab, you *must* declare them. With the query in Design view, choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
users
to recognize
the
Click to expand...
Click to expand...
 
Thank you, that worked. Although the parameter window pops-up on closure. Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that will
satisfy all. Thanks so much for your help.


John Viescas said:
Declaring the parameters is not the same as specifying the values. You MUST
declare your parameters ([Type the beginning date], [Type the ending date])
in a crosstab. Open the query in design view. Choose Parameters from the
query menu. On the first line in the Parameters dialog box, type: [Type the
beginning date] and select Date/Time as the data type. Do the same thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
That's the problem. I don't want to declare specific parameters. How
Click to expand...
would
I
use (Between [Type the beginning date] And [Type the ending date])? Access
won't accept it in the parameter's grid.

John Viescas said:
Donna-

You should always explicitly declare parameters in any query, but in a
Crosstab, you *must* declare them. With the query in Design view, choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need for my users
to
be able to put in a date selection but the jet engine doesn't recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
The typical way to do this is to create an unbound form where the user can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks to see
if the form you reference is open and that it contains a control with the
name you specify. If not, it'll prompt you for the value, just like it does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog. Provide a
command button on the form that runs code to verify the two date values and
hide the form (Me.Visible = False) to allow the report to continue. In the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Thank you, that worked. Although the parameter window pops-up on closure. Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that will
satisfy all. Thanks so much for your help.


John Viescas said:
Declaring the parameters is not the same as specifying the values. You MUST
declare your parameters ([Type the beginning date], [Type the ending date])
in a crosstab. Open the query in design view. Choose Parameters from the
query menu. On the first line in the Parameters dialog box, type: [Type the
beginning date] and select Date/Time as the data type. Do the same thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
That's the problem. I don't want to declare specific parameters. How
Click to expand...
would
I
use (Between [Type the beginning date] And [Type the ending date])? Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query, but in a
Crosstab, you *must* declare them. With the query in Design view, choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need for my users
to
be able to put in a date selection but the jet engine doesn't recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
Well, I tried what you suggested but I must of missed something because I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any input
of dates. Is there any reading material you can direct me to regarding this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


John Viescas said:
The typical way to do this is to create an unbound form where the user can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks to see
if the form you reference is open and that it contains a control with the
name you specify. If not, it'll prompt you for the value, just like it does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog. Provide a
command button on the form that runs code to verify the two date values and
hide the form (Me.Visible = False) to allow the report to continue. In the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Thank you, that worked. Although the parameter window pops-up on
Click to expand...
closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that will
satisfy all. Thanks so much for your help.


John Viescas said:
Declaring the parameters is not the same as specifying the values.
Click to expand...
You
MUST
declare your parameters ([Type the beginning date], [Type the ending date])
in a crosstab. Open the query in design view. Choose Parameters from the
query menu. On the first line in the Parameters dialog box, type:
Click to expand...
[Type
the
beginning date] and select Date/Time as the data type. Do the same thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters. How would
I
use (Between [Type the beginning date] And [Type the ending date])? Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query, but
Click to expand...
Click to expand...
in
a
Click to expand...
Click to expand...
 
When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It should
be a plain unbound form (nothing in the Record Source) with two unbound text
boxes (nothing in the Control Source). You can define an Input Mask for the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Well, I tried what you suggested but I must of missed something because I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any input
of dates. Is there any reading material you can direct me to regarding this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


John Viescas said:
The typical way to do this is to create an unbound form where the user can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks to see
if the form you reference is open and that it contains a control with the
name you specify. If not, it'll prompt you for the value, just like it does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog. Provide a
command button on the form that runs code to verify the two date values and
hide the form (Me.Visible = False) to allow the report to continue. In the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Thank you, that worked. Although the parameter window pops-up on
Click to expand...
closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates.
Click to expand...
Right
now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values. You
MUST
declare your parameters ([Type the beginning date], [Type the ending
date])
in a crosstab. Open the query in design view. Choose Parameters
Click to expand...
from
the
query menu. On the first line in the Parameters dialog box, type: [Type
the
beginning date] and select Date/Time as the data type. Do the same thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters. How
would
I
use (Between [Type the beginning date] And [Type the ending date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query,
Click to expand...
Click to expand...
but
in
Click to expand...
for
my
Click to expand...
Click to expand...
 
This is hard when you don't code. I cut and pasted your code. The unbound
form comes up with the error "you must enter valid dates" when I input
01/01/03 and 06/30/03. I'll keep working on it but I can't see what is
wrong. I defined an Input Mask and set the format to short date. I'm getting
closer but it is frustrating due to my lack of knowledge. Again thanks for
your patience and help.


John Viescas said:
When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It should
be a plain unbound form (nothing in the Record Source) with two unbound text
boxes (nothing in the Control Source). You can define an Input Mask for the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Well, I tried what you suggested but I must of missed something because I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any input
of dates. Is there any reading material you can direct me to regarding this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


John Viescas said:
The typical way to do this is to create an unbound form where the user can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks
Click to expand...
to
see
if the form you reference is open and that it contains a control with the
name you specify. If not, it'll prompt you for the value, just like
Click to expand...
it
does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog. Provide a
command button on the form that runs code to verify the two date
Click to expand...
values
and
hide the form (Me.Visible = False) to allow the report to continue.
Click to expand...
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up on closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right
now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values. You
MUST
declare your parameters ([Type the beginning date], [Type the ending
date])
in a crosstab. Open the query in design view. Choose Parameters from
the
query menu. On the first line in the Parameters dialog box, type: [Type
the
beginning date] and select Date/Time as the data type. Do the same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters. How
would
I
use (Between [Type the beginning date] And [Type the ending date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query,
Click to expand...
but
in
a
Crosstab, you *must* declare them. With the query in Design view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need
Click to expand...
for
my
users
to
be able to put in a date selection but the jet engine doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
I just made some changes so that the validation on the unbound form now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field name or
expression. I'll keep working on it. Again any help is appreciated.

John Viescas said:
When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It should
be a plain unbound form (nothing in the Record Source) with two unbound text
boxes (nothing in the Control Source). You can define an Input Mask for the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Well, I tried what you suggested but I must of missed something because I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any input
of dates. Is there any reading material you can direct me to regarding this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


John Viescas said:
The typical way to do this is to create an unbound form where the user can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks
Click to expand...
to
see
if the form you reference is open and that it contains a control with the
name you specify. If not, it'll prompt you for the value, just like
Click to expand...
it
does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog. Provide a
command button on the form that runs code to verify the two date
Click to expand...
values
and
hide the form (Me.Visible = False) to allow the report to continue.
Click to expand...
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up on closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right
now
I'm getting four parameter windows that I have to put the same range of
dates. Surely, there is a way that I can get one parameter window that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values. You
MUST
declare your parameters ([Type the beginning date], [Type the ending
date])
in a crosstab. Open the query in design view. Choose Parameters from
the
query menu. On the first line in the Parameters dialog box, type: [Type
the
beginning date] and select Date/Time as the data type. Do the same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters. How
would
I
use (Between [Type the beginning date] And [Type the ending date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query,
Click to expand...
but
in
a
Crosstab, you *must* declare them. With the query in Design view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need
Click to expand...
for
my
users
to
be able to put in a date selection but the jet engine doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters. When you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]), did you go
back to Query / Parameters and change the names there? If not, that's the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
I just made some changes so that the validation on the unbound form now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field name or
expression. I'll keep working on it. Again any help is appreciated.

John Viescas said:
When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It should
be a plain unbound form (nothing in the Record Source) with two unbound text
boxes (nothing in the Control Source). You can define an Input Mask for the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Click to expand...
because
I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any input
of dates. Is there any reading material you can direct me to regarding this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form where the
Click to expand...
user
can
enter beginning and ending dates in unbound text boxes. Change your query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first
Click to expand...
Click to expand...
checks
to with
the
Click to expand...
Provide
a
command button on the form that runs code to verify the two date values
and
hide the form (Me.Visible = False) to allow the report to continue. In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates. Right
now
I'm getting four parameter windows that I have to put the same
Click to expand...
range
of
dates. Surely, there is a way that I can get one parameter window that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values.
You
MUST
declare your parameters ([Type the beginning date], [Type the ending
date])
in a crosstab. Open the query in design view. Choose
Click to expand...
Parameters
from
the
query menu. On the first line in the Parameters dialog box, type:
[Type
the
beginning date] and select Date/Time as the data type. Do the same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific
Click to expand...
parameters.
How
would
I
use (Between [Type the beginning date] And [Type the ending date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any
Click to expand...
query,
but
in
a
Crosstab, you *must* declare them. With the query in Design view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I
Click to expand...
need
for
my
users
to
be able to put in a date selection but the jet engine doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
I did. It didn't like ([Forms]![frmDatePrompt]![txtBegin]), it asks for the
value. So I tried [txtBegin] and then[txtEnd]. It doesn't prompt me for a
value now but I still get the error that the jet engine doesn't recognize
([Forms]![frmDatePrompt]![txtBegin]). I too think that the problem is here.
Why does it not recognize ([Forms]![frmDatePrompt]![txtBegin])?

John Viescas said:
Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters. When you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]), did you go
back to Query / Parameters and change the names there? If not, that's the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
I just made some changes so that the validation on the unbound form now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field name or
expression. I'll keep working on it. Again any help is appreciated.

John Viescas said:
When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It should
be a plain unbound form (nothing in the Record Source) with two
Click to expand...
unbound
text
boxes (nothing in the Control Source). You can define an Input Mask
Click to expand...
for
the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Well, I tried what you suggested but I must of missed something
Click to expand...
because
I
get an error message that the jet database engine does not recognize the
expression you gave me below. I'm sure my igonorance is the problem. I
created the form but from what I can tell the form isn't allowing any
input
of dates. Is there any reading material you can direct me to regarding
this
type of form? Your help is greatly appreciated. This is a little database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form where the user
can
enter beginning and ending dates in unbound text boxes. Change your
query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first
Click to expand...
checks
to
see
if the form you reference is open and that it contains a control with
the
name you specify. If not, it'll prompt you for the value, just
Click to expand...
like
it
does
now. If it does find the form and control, it won't prompt you at all.

In the Open event of the report, open this form as a Dialog.
Click to expand...
Provide
a
command button on the form that runs code to verify the two date values
and
hide the form (Me.Visible = False) to allow the report to
Click to expand...
continue.
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates.
Right
now
I'm getting four parameter windows that I have to put the same range
of
dates. Surely, there is a way that I can get one parameter
Click to expand...
window
that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values.
You
MUST
declare your parameters ([Type the beginning date], [Type the ending
date])
in a crosstab. Open the query in design view. Choose Parameters
from
the
query menu. On the first line in the Parameters dialog box, type:
[Type
the
beginning date] and select Date/Time as the data type. Do the same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters.
How
would
I
use (Between [Type the beginning date] And [Type the ending
date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query,
but
in
a
Crosstab, you *must* declare them. With the query in Design
view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need
for
my
users
to
be able to put in a date selection but the jet engine doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
Open your query in Design View, choose SQL from the View menu, and paste the
text into a reply. If the SQL is correct, it should recognize the two text
boxes as long as your form is actually named frmDatePrompt, the two text
boxes are named txtBegin and txtEnd, AND the form is open when Access tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
I did. It didn't like ([Forms]![frmDatePrompt]![txtBegin]), it asks for the
value. So I tried [txtBegin] and then[txtEnd]. It doesn't prompt me for a
value now but I still get the error that the jet engine doesn't recognize
([Forms]![frmDatePrompt]![txtBegin]). I too think that the problem is here.
Why does it not recognize ([Forms]![frmDatePrompt]![txtBegin])?

John Viescas said:
Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters. When you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]), did
Click to expand...
you
go
back to Query / Parameters and change the names there? If not, that's the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
I just made some changes so that the validation on the unbound form now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field name or
expression. I'll keep working on it. Again any help is appreciated.

When you changed the query to point to the form, did you also change the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It
should
be a plain unbound form (nothing in the Record Source) with two unbound
text
boxes (nothing in the Control Source). You can define an Input Mask for
the
text boxes to force the user to enter a valid mm/dd/yyyy value - and also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Well, I tried what you suggested but I must of missed something because
I
get an error message that the jet database engine does not
Click to expand...
recognize
the
expression you gave me below. I'm sure my igonorance is the
Click to expand...
Click to expand...
problem.
I
created the form but from what I can tell the form isn't allowing any
input
of dates. Is there any reading material you can direct me to regarding
this
type of form? Your help is greatly appreciated. This is a little
database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form where
Click to expand...
the
user
can
enter beginning and ending dates in unbound text boxes. Change your
query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd,
Click to expand...
then
your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the beginning
date].

When you create a parameter that looks like this, Access first checks
to
see
if the form you reference is open and that it contains a control with
the
name you specify. If not, it'll prompt you for the value, just like
it
does
now. If it does find the form and control, it won't prompt you at
all.

In the Open event of the report, open this form as a Dialog. Provide
a
command button on the form that runs code to verify the two date
values
and
hide the form (Me.Visible = False) to allow the report to continue.
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3 subreports
attached to the main report which all use the same range of dates.
Right
now
I'm getting four parameter windows that I have to put the same range
of
dates. Surely, there is a way that I can get one parameter window
that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the values.
You
MUST
declare your parameters ([Type the beginning date], [Type the
ending
date])
in a crosstab. Open the query in design view. Choose Parameters
from
the
query menu. On the first line in the Parameters dialog box, type:
[Type
the
beginning date] and select Date/Time as the data type. Do the
same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific parameters.
How
would
I
use (Between [Type the beginning date] And [Type the ending
date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any query,
but
in
a
Crosstab, you *must* declare them. With the query in Design
view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I need
for
my
users
to
be able to put in a date selection but the jet engine
doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
Open your query in Design View, choose SQL from the View menu, and paste the
text into a reply. If the SQL is correct, it should recognize the two text
boxes as long as your form is actually named frmDatePrompt, the two text
boxes are named txtBegin and txtEnd, AND the form is open when Access tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
I did. It didn't like ([Forms]![frmDatePrompt]![txtBegin]), it asks for the
value. So I tried [txtBegin] and then[txtEnd]. It doesn't prompt me for a
value now but I still get the error that the jet engine doesn't recognize
([Forms]![frmDatePrompt]![txtBegin]). I too think that the problem is here.
Why does it not recognize ([Forms]![frmDatePrompt]![txtBegin])?

John Viescas said:
Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters.
Click to expand...
When
you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]), did
Click to expand...
you
go
back to Query / Parameters and change the names there? If not, that's the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
I just made some changes so that the validation on the unbound form now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field name or
expression. I'll keep working on it. Again any help is appreciated.

When you changed the query to point to the form, did you also
Click to expand...
change
the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?" It
should
be a plain unbound form (nothing in the Record Source) with two unbound
text
boxes (nothing in the Control Source). You can define an Input
Click to expand...
Mask
for
the
text boxes to force the user to enter a valid mm/dd/yyyy value - and
also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Well, I tried what you suggested but I must of missed something
because
I
get an error message that the jet database engine does not recognize
the
expression you gave me below. I'm sure my igonorance is the
Click to expand...
problem.
I
created the form but from what I can tell the form isn't
Click to expand...
allowing
any
input
of dates. Is there any reading material you can direct me to regarding
this
type of form? Your help is greatly appreciated. This is a little
database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form where the
user
can
enter beginning and ending dates in unbound text boxes.
Click to expand...
Change
your
query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then
your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the
beginning
date].

When you create a parameter that looks like this, Access first
checks
to
see
if the form you reference is open and that it contains a control
with
the
name you specify. If not, it'll prompt you for the value,
Click to expand...
just
like
it
does
now. If it does find the form and control, it won't prompt
Click to expand...
Click to expand...
you
at
Click to expand...
pops-up
on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3
subreports
attached to the main report which all use the same range of dates.
Right
now
I'm getting four parameter windows that I have to put the same
range
of
dates. Surely, there is a way that I can get one parameter window
that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the
values.
You
MUST
declare your parameters ([Type the beginning date], [Type the
ending
date])
in a crosstab. Open the query in design view. Choose
Parameters
from
the
query menu. On the first line in the Parameters dialog box,
type:
[Type
the
beginning date] and select Date/Time as the data type. Do the
same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific
parameters.
How
would
I
use (Between [Type the beginning date] And [Type the ending
date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any
query,
but
in
a
Crosstab, you *must* declare them. With the query in Design
view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab query? I
need
for
my
users
to
be able to put in a date selection but the jet engine
doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first line to this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
Open your query in Design View, choose SQL from the View menu, and paste the
text into a reply. If the SQL is correct, it should recognize the two text
boxes as long as your form is actually named frmDatePrompt, the two text
boxes are named txtBegin and txtEnd, AND the form is open when Access tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
I did. It didn't like ([Forms]![frmDatePrompt]![txtBegin]), it asks
Click to expand...
for
the
value. So I tried [txtBegin] and then[txtEnd]. It doesn't prompt me
Click to expand...
Click to expand...
for
a
value now but I still get the error that the jet engine doesn't recognize
([Forms]![frmDatePrompt]![txtBegin]). I too think that the problem is here.
Why does it not recognize ([Forms]![frmDatePrompt]![txtBegin])?

Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters. When
you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]),
Click to expand...
did
you
go
back to Query / Parameters and change the names there? If not,
Click to expand...
that's
the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
I just made some changes so that the validation on the unbound
Click to expand...
form
now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field
Click to expand...
Click to expand...
name
or
expression. I'll keep working on it. Again any help is appreciated.

When you changed the query to point to the form, did you also change
the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of
Click to expand...
dates?"
It
should
be a plain unbound form (nothing in the Record Source) with two
unbound
text
boxes (nothing in the Control Source). You can define an Input Mask
for
the
text boxes to force the user to enter a valid mm/dd/yyyy value - and
also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Well, I tried what you suggested but I must of missed something
because
I
get an error message that the jet database engine does not recognize
the
expression you gave me below. I'm sure my igonorance is the problem.
I
created the form but from what I can tell the form isn't allowing
any
input
of dates. Is there any reading material you can direct me to
regarding
this
type of form? Your help is greatly appreciated. This is a little
database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form
Click to expand...
where
the
user
can
enter beginning and ending dates in unbound text boxes. Change
your
query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd, then
your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the
beginning
date].

When you create a parameter that looks like this, Access first
checks
to
see
if the form you reference is open and that it contains a control
with
the
name you specify. If not, it'll prompt you for the value, just
like
it
does
now. If it does find the form and control, it won't prompt
Click to expand...
you
at
all.

In the Open event of the report, open this form as a Dialog.
Provide
a
command button on the form that runs code to verify the two date
values
and
hide the form (Me.Visible = False) to allow the report to
continue.
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window
Click to expand...
pops-up
on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3
subreports
attached to the main report which all use the same range of
dates.
Right
now
I'm getting four parameter windows that I have to put the same
range
of
dates. Surely, there is a way that I can get one parameter
window
that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the
values.
You
MUST
declare your parameters ([Type the beginning date],
Click to expand...
[Type
the
ending
date])
in a crosstab. Open the query in design view. Choose
Parameters
from
the
query menu. On the first line in the Parameters dialog box,
type:
[Type
the
beginning date] and select Date/Time as the data type.
Click to expand...
Do
the
same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific
parameters.
How
would
I
use (Between [Type the beginning date] And [Type the ending
date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any
query,
but
in
a
Crosstab, you *must* declare them. With the query in
Design
view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab
Click to expand...
Click to expand...
query?
I
Click to expand...
Click to expand...
 
I did do it, but as I said a couple of posts ago, it would come up with a
prompt for the value. But I will try it again and send you the SQL.

John Viescas said:
I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first line to this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
Open your query in Design View, choose SQL from the View menu, and
Click to expand...
paste
the
text into a reply. If the SQL is correct, it should recognize the two text
boxes as long as your form is actually named frmDatePrompt, the two text
boxes are named txtBegin and txtEnd, AND the form is open when Access tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
I did. It didn't like ([Forms]![frmDatePrompt]![txtBegin]), it asks for
the
value. So I tried [txtBegin] and then[txtEnd]. It doesn't prompt me
Click to expand...
for
a
value now but I still get the error that the jet engine doesn't recognize
([Forms]![frmDatePrompt]![txtBegin]). I too think that the problem is
here.
Why does it not recognize ([Forms]![frmDatePrompt]![txtBegin])?

Sounds like you got your form problem solved, but you need to fix your
query. I originally told you to explicitly define the parameters. When
you
changed the parameter names ([Forms]![frmDatePrompt]![txtBegin]), did
you
go
back to Query / Parameters and change the names there? If not, that's
the
problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
I just made some changes so that the validation on the unbound form
now
works. But I'm still getting microsoft jet database engine does not
recognize [Forms]![frmDatePrompt]![txtBegin] as a valid field
Click to expand...
name
or
expression. I'll keep working on it. Again any help is appreciated.

When you changed the query to point to the form, did you also change
the
explicitly declared parameters?

What do you mean by "the form isn't allowing any input of dates?"
It
should
be a plain unbound form (nothing in the Record Source) with two
unbound
text
boxes (nothing in the Control Source). You can define an
Click to expand...
Input
Mask
for
the
text boxes to force the user to enter a valid mm/dd/yyyy
Click to expand...
value -
and
also
set the Format to something like Short Date.

Code in the Open event of the report should look like:

Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub


Code behind and "OK" button on the form that the user can click
after
entering the dates should look like:

Private Sub cmdOK_Click()

' Validate the dates

If Not (IsDate(Me.txtBegin) And IsDate(Me.txtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


And finally, the Close event of the report should look like:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Well, I tried what you suggested but I must of missed something
because
I
get an error message that the jet database engine does not
recognize
the
expression you gave me below. I'm sure my igonorance is the
problem.
I
created the form but from what I can tell the form isn't allowing
any
input
of dates. Is there any reading material you can direct me to
regarding
this
type of form? Your help is greatly appreciated. This is a little
database
and being able to do this for my friend would be great.


The typical way to do this is to create an unbound form where
the
user
can
enter beginning and ending dates in unbound text boxes. Change
your
query
parameters to point to these two values. If your form is called
"frmDatePrompt" and your text boxes are txtBegin and txtEnd,
then
your
parameter will look like:

[Forms]![frmDatePrompt]![txtBegin] ... instead of [Type the
beginning
date].

When you create a parameter that looks like this, Access first
checks
to
see
if the form you reference is open and that it contains a control
with
the
name you specify. If not, it'll prompt you for the value, just
like
it
does
now. If it does find the form and control, it won't
Click to expand...
prompt
you
at
all.

In the Open event of the report, open this form as a Dialog.
Provide
a
command button on the form that runs code to verify the
Click to expand...
two
date
values
and
hide the form (Me.Visible = False) to allow the report to
continue.
In
the
Close event of the report, close the form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Thank you, that worked. Although the parameter window pops-up
on
closure.
Is
this normal?

Now what would be the best way to run a report that has 3
subreports
attached to the main report which all use the same range of
dates.
Right
now
I'm getting four parameter windows that I have to put
Click to expand...
the
same
range
of
dates. Surely, there is a way that I can get one parameter
window
that
will
satisfy all. Thanks so much for your help.


Declaring the parameters is not the same as specifying the
values.
You
MUST
declare your parameters ([Type the beginning date], [Type
the
ending
date])
in a crosstab. Open the query in design view. Choose
Parameters
from
the
query menu. On the first line in the Parameters
Click to expand...
dialog
box,
type:
[Type
the
beginning date] and select Date/Time as the data type. Do
the
same
thing
for the second parameter on the second line.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)


http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
That's the problem. I don't want to declare specific
parameters.
How
would
I
use (Between [Type the beginning date] And [Type the
ending
date])?
Access
won't accept it in the parameter's grid.

Donna-

You should always explicitly declare parameters in any
query,
but
in
a
Crosstab, you *must* declare them. With the query in
Design
view,
choose
Parameters from the Query menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
How can I query for parameters in a cross-tab
Click to expand...
query?
I
need
for
my
users
to
be able to put in a date selection but the jet engine
doesn't
recognize
the
request? Any help would be appreciated.

Thanks,
Donna
Click to expand...
Click to expand...
Click to expand...
 
The SQL you posted says you didn't... <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
I did do it, but as I said a couple of posts ago, it would come up with a
prompt for the value. But I will try it again and send you the SQL.

John Viescas said:
I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first line to this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
djhsmith23 said:
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And
Click to expand...
[Forms]![frmDatePrompt]![TxtEnd])
AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

Open your query in Design View, choose SQL from the View menu, and paste
the
text into a reply. If the SQL is correct, it should recognize the two
text
boxes as long as your form is actually named frmDatePrompt, the two text
boxes are named txtBegin and txtEnd, AND the form is open when Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
Here's the SQL you wanted, it doesn't work either. With this I get a prompt
for a value for both parameters. If I just hit enter, it takes me to the
DatePrompt form where I enter the dates, and then I get the jet engine
doesn't recognize " as a valid field, etc.

PARAMETERS [Forms]![frmDatePrompt]![TxtBegin] DateTime,
[Forms]![frmDatePrompt]![TxtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
The SQL you posted says you didn't... <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
I did do it, but as I said a couple of posts ago, it would come up with a
prompt for the value. But I will try it again and send you the SQL.

John Viescas said:
I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first line to this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd])
AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

Open your query in Design View, choose SQL from the View menu, and paste
the
text into a reply. If the SQL is correct, it should recognize the two
text
boxes as long as your form is actually named frmDatePrompt, the
Click to expand...
two
text
boxes are named txtBegin and txtEnd, AND the form is open when Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
OK, this is the record source for a report, right? Did you add code in the
report's Open event to open the form as a Dialog as I showed you? Please
post the VB code from behind the report and also the VB code from behind the
frmDatePrompt form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
Here's the SQL you wanted, it doesn't work either. With this I get a prompt
for a value for both parameters. If I just hit enter, it takes me to the
DatePrompt form where I enter the dates, and then I get the jet engine
doesn't recognize " as a valid field, etc.

PARAMETERS [Forms]![frmDatePrompt]![TxtBegin] DateTime,
[Forms]![frmDatePrompt]![TxtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
The SQL you posted says you didn't... <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Click to expand...
with
a
prompt for the value. But I will try it again and send you the SQL.

I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first line to
this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST
RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd])
AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST
RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST
RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

Open your query in Design View, choose SQL from the View menu, and
paste
the
text into a reply. If the SQL is correct, it should recognize
Click to expand...
the
two
text
boxes as long as your form is actually named frmDatePrompt, the two
text
boxes are named txtBegin and txtEnd, AND the form is open when Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
Here is the code behind the report:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub

Here's the code behind the OK command button for the frmDatePrompt.

Private Sub Command4_Click()

' Validate the dates

If Not (IsDate(Me.TxtBegin) And IsDate(Me.TxtEnd)) Then

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


John Viescas said:
OK, this is the record source for a report, right? Did you add code in the
report's Open event to open the form as a Dialog as I showed you? Please
post the VB code from behind the report and also the VB code from behind the
frmDatePrompt form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
DSmith said:
Here's the SQL you wanted, it doesn't work either. With this I get a prompt
for a value for both parameters. If I just hit enter, it takes me to the
DatePrompt form where I enter the dates, and then I get the jet engine
doesn't recognize " as a valid field, etc.

PARAMETERS [Forms]![frmDatePrompt]![TxtBegin] DateTime,
[Forms]![frmDatePrompt]![TxtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And [Forms]![frmDatePrompt]![TxtEnd]) AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or ([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director, [TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

John Viescas said:
The SQL you posted says you didn't... <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
I did do it, but as I said a couple of posts ago, it would come up
Click to expand...
with
a
prompt for the value. But I will try it again and send you the SQL.

I told you two posts ago to change the parameter declarations, and you
didn't do it. That's why it is not working. Change the first
Click to expand...
Click to expand...
line
to
this:

PARAMETERS [Forms]![frmDatePrompt]![txtBegin] DateTime,
[Forms]![frmDatePrompt]![txtEnd] DateTime;


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST
RESULTS].ExamDate
FROM [TECH TEST RESULTS]
WHERE ((([TECH TEST RESULTS].ExamDate) Between
[Forms]![frmDatePrompt]![TxtBegin] And
[Forms]![frmDatePrompt]![TxtEnd])
AND
(([TECH TEST RESULTS].ExamCode)="C" Or ([TECH TEST
RESULTS].ExamCode)="CH"
Or ([TECH TEST RESULTS].ExamCode)="EXT" Or ([TECH TEST
RESULTS].ExamCode)="SK" Or ([TECH TEST RESULTS].ExamCode)="SP" Or
([TECH
TEST RESULTS].ExamCode)="POD" Or ([TECH TEST RESULTS].ExamCode)="BD"))
GROUP BY [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName, [TECH TEST
RESULTS].FirstName, [TECH TEST RESULTS].SSN, [TECH TEST
RESULTS].ExamDate
PIVOT [TECH TEST RESULTS].ExamCode;

Open your query in Design View, choose SQL from the View menu, and
paste
the
text into a reply. If the SQL is correct, it should recognize the
two
text
boxes as long as your form is actually named frmDatePrompt,
Click to expand...
the
two
text
boxes are named txtBegin and txtEnd, AND the form is open when
Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top