Cross-tab query parameters

[John Viescas]

That should work. When you open the report, you should first see the
frmDatePrompt form open. Enter the two dates and click OK. The form should
disappear and the report open filtered for those dates. What is actually
happening? Please post the exact sequence of steps you're taking
(frmDatePrompt should not be open already when you try this) and the exact
text of any error messages.

Something else to try: Open frmDatePrompt, type in two dates, but do NOT
click the command button. Go to the database window and open the query. If
the query runs OK but the report does not, then there might be a problem
with your report design.

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

 

[djhsmith23]

Oh, good, then maybe I'm not a complete idiot.
This is what is happening:
1. Open the report
2. Date Prompt form opens
3. Enter two dates
4. Click OK
5. Error: The Microsoft Jet database engine does not recognize " as a valid
field name or expression.

I opened the DatePrompt form as you requested and did not click the command
button, but I could not go to the database window without closing the
DatePrompt form. So I just went directly to the query and opened it. The
prompts for dates came up so I entered two dates. The query worked fine so
the problem must be in the report. The report worked before the DatePrompt
form. I will look again thru everything to see if there is something that
shouldn't be there in the new coding. Where would (not recognize ") be
pointing to? Is there a set of quotation marks out of place maybe? As always
thank you for your time and expertise.

That should work. When you open the report, you should first see the
frmDatePrompt form open. Enter the two dates and click OK. The form should
disappear and the report open filtered for those dates. What is actually
happening? Please post the exact sequence of steps you're taking
(frmDatePrompt should not be open already when you try this) and the exact
text of any error messages.

Something else to try: Open frmDatePrompt, type in two dates, but do NOT
click the command button. Go to the database window and open the query. If
the query runs OK but the report does not, then there might be a problem
with your report design.

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

 

[djhsmith23]

I found the culprit. On the report, I had a label for the dates entered and
I hadn't changed it to point to the frmDatePrompt. It still was [Type in the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.

Oh, good, then maybe I'm not a complete idiot.
This is what is happening:
1. Open the report
2. Date Prompt form opens
3. Enter two dates
4. Click OK
5. Error: The Microsoft Jet database engine does not recognize " as a valid
field name or expression.

I opened the DatePrompt form as you requested and did not click the command
button, but I could not go to the database window without closing the
DatePrompt form. So I just went directly to the query and opened it. The
prompts for dates came up so I entered two dates. The query worked fine so
the problem must be in the report. The report worked before the DatePrompt
form. I will look again thru everything to see if there is something that
shouldn't be there in the new coding. Where would (not recognize ") be
pointing to? Is there a set of quotation marks out of place maybe? As always
thank you for your time and expertise.

That should work. When you open the report, you should first see the
frmDatePrompt form open. Enter the two dates and click OK. The form should
disappear and the report open filtered for those dates. What is actually
happening? Please post the exact sequence of steps you're taking
(frmDatePrompt should not be open already when you try this) and the exact
text of any error messages.

Something else to try: Open frmDatePrompt, type in two dates, but do NOT
click the command button. Go to the database window and open the query. If
the query runs OK but the report does not, then there might be a problem
with your report design.

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

 

[John Viescas]

Huzzah!

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

In this same query, I need to return zeros when a column heading doesn't
return any data. In another message to someone else, you had suggested:
NZ(Sum[AmountSold]), 0). I tried NZ(Sum[#correct]), 0 in my Value column. I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.

Huzzah!

--
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 found the culprit. On the report, I had a label for the dates entered and
I hadn't changed it to point to the frmDatePrompt. It still was [Type in the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.

 

[John Viescas]

If you typed it exactly as you posted in your message, you're missing some
parens:

NZ(Sum([#correct]), 0)

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

In this same query, I need to return zeros when a column heading doesn't
return any data. In another message to someone else, you had suggested:
NZ(Sum[AmountSold]), 0). I tried NZ(Sum[#correct]), 0 in my Value column. I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.

Huzzah!

--
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 found the culprit. On the report, I had a label for the dates

entered
and

I hadn't changed it to point to the frmDatePrompt. It still was [Type

in
the

beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.

 

[DSmith]

Oops, wish it was that easy--not missing in the query.

If you typed it exactly as you posted in your message, you're missing some
parens:

NZ(Sum([#correct]), 0)

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

In this same query, I need to return zeros when a column heading doesn't
return any data. In another message to someone else, you had suggested:
NZ(Sum[AmountSold]), 0). I tried NZ(Sum[#correct]), 0 in my Value

column.

I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.

[Type

in

the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.

 

[DSmith]

Ok (must of left something off), I went back re-entered and now I get:
Can't have aggregate function in expression (Avg(NZ(Sum([#Correct]), 0))

If you typed it exactly as you posted in your message, you're missing some
parens:

NZ(Sum([#correct]), 0)

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

In this same query, I need to return zeros when a column heading doesn't
return any data. In another message to someone else, you had suggested:
NZ(Sum[AmountSold]), 0). I tried NZ(Sum[#correct]), 0 in my Value

column.

I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.

[Type

in

the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.