Cross-tab query parameters

  • Thread starter Thread starter djhsmith23
  • Start date Start date
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
DSmith said:
Here is the code behind the report:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub

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

Private Sub Command4_Click()

' Validate the dates

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

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


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

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

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

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,
Click to expand...
and
you
didn't do it. That's why it is not working. Change the first
Click to expand...
line
to
this:

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


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

http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Here is the SQL:
PARAMETERS [txtBegin] DateTime, [txtEnd] DateTime;
TRANSFORM Avg([TECH TEST RESULTS].[#Correct]) AS [The Value]
SELECT [TECH TEST RESULTS].SchoolName, [TECH TEST RESULTS].Director,
[TECH
TEST RESULTS].TestAttempts, [TECH TEST RESULTS].LastName,
Click to expand...
[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,
Click to expand...
[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
Click to expand...
menu,
and
paste
the
text into a reply. If the SQL is correct, it should
Click to expand...
recognize
the
two
text
boxes as long as your form is actually named frmDatePrompt, the
two
text
boxes are named txtBegin and txtEnd, AND the form is open when
Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
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.

John Viescas said:
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
DSmith said:
Here is the code behind the report:

Private Sub Report_Close()

' Close the hidden date form

DoCmd.Close acForm, "frmDatePrompt"

End Sub


Private Sub Report_Open(Cancel As Integer)

' Open the date range dialog

' .. report record source is filtered on this!

DoCmd.OpenForm "frmDatePrompt", WindowMode:=acDialog

End Sub

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

Private Sub Command4_Click()

' Validate the dates

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

MsgBox "You must enter valid dates."

Exit Sub

End If

' Good dates - now hide me so report can finish

Me.Visible = False

End Sub


John Viescas said:
OK, this is the record source for a report, right? Did you add code
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
frmDatePrompt,
the
two
text
boxes are named txtBegin and txtEnd, AND the form is open when
Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
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.

djhsmith23 said:
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.

John Viescas said:
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
DSmith said:
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
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
open
when
Access
tries
to resolve the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
Click to expand...
 
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.

John Viescas said:
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
djhsmith23 said:
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.
Click to expand...
Click to expand...
 
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
DSmith said:
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.

John Viescas said:
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
djhsmith23 said:
I found the culprit. On the report, I had a label for the dates
Click to expand...
entered
and
I hadn't changed it to point to the frmDatePrompt. It still was [Type
Click to expand...
in
the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.
Click to expand...
Click to expand...
Click to expand...
 
Oops, wish it was that easy--not missing in the query.

John Viescas said:
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
DSmith said:
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
Click to expand...
column.
I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.
Click to expand...
[Type
in
the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.
Click to expand...
Click to expand...
Click to expand...
 
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))

John Viescas said:
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
DSmith said:
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
Click to expand...
column.
I
get: The expression you entered contains invalid syntax. Any help would be
appreciated.
Click to expand...
[Type
in
the
beginning date], etc. So now the report works and I learned a lot to boot.
Thank you so much.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top