Cross-tab query parameters

[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

 

[John Viescas]

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

 

[djhsmith23]

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]

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
users
to recognize
the

 

[djhsmith23]

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

 

[John Viescas]

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

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

 

[djhsmith23]

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 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

 

[John Viescas]

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 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 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

for

my

 

[djhsmith23]

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.

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 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 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

 

[djhsmith23]

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 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 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

 

[John Viescas]

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 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

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 with
the

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

 

[DSmith]

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])?

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 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 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 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

 

[John Viescas]

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

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])?

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 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

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 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

 

[djhsmith23]

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 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 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 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 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 prompt

you

at

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

 

[John Viescas]

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 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

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

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 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 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

 

[DSmith]

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.

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 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

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

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
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

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

 

[John Viescas]

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 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 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)

 

[DSmith]

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;

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 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 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)

 

[John Viescas]

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

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;

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

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

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)

 

[DSmith]

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

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

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;

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

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 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)