Syntax error in Dsum

[Claudette Hennessy]

I have a field in a Report Footer which should calculate a particular Total
Attendance.

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=#1/1/2007# and
[EventDate]<=#6/1/2007#") works fine.
However the report runs off of a dialog form which chooses the dates. I
have not been able to substitute frmEventReportDialog!txtBeginDate for
1/1/2007 and frmEventReportDialog!txtEndDate for 6/1/2007.
For instance,
=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >= #" &
Forms!frmEventReportDialog!txtBeginDate & "# and [EventDate] >= #" &
Forms!frmEventReportDialog!txtEndDate & "#") is one of the many permutations
of &'s and "'s I have tried.
Thank you in advance,
Claudette Hennessy

 

[Ken Snell \(MVP\)]

Set the Format property of the textbox on the form to Short Date to help Jet
know what data type you want.

Also, change the DSum expression to this:

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=
CDate(Forms!frmEventReportDialog!txtBeginDate) & " and [EventDate] >= " &
CDate(Forms!frmEventReportDialog!txtEndDate)

 

[Duane Hookom]

Assuming qryDateEventAttendance isn't the record source of the report, I
would create a query like:
SELECT Sum(Attendance) as AttendSum
FROM qryDateEventAttendance
WHERE EventDate BETWEEN Forms!frmEventReportDialog!txtBeginDate AND
Forms!frmEventReportDialog!txtEndDate ;

You can then add this query which returns a single record to your report's
record source and bind the AttendSum in your report.

 

[Claudette Hennessy]

You are a lifesaver. Setting the format in the dialog form is key, as
testing by just running the report and inputting the begin and end dates
from the parameter screen gives an error. The code that I actually got to
work is :
=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=
CDate(Forms!frmEventReportDialog!txtBeginDate) " & " And [EventDate] <=
CDate(Forms!frmEventReportDialog!txtEndDate) ")

Set the Format property of the textbox on the form to Short Date to help
Jet know what data type you want.

Also, change the DSum expression to this:

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=
CDate(Forms!frmEventReportDialog!txtBeginDate) & " and [EventDate] >= " &
CDate(Forms!frmEventReportDialog!txtEndDate)

--

Ken Snell
<MS ACCESS MVP>

I have a field in a Report Footer which should calculate a particular
Total Attendance.

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=#1/1/2007#
and [EventDate]<=#6/1/2007#") works fine.
However the report runs off of a dialog form which chooses the dates. I
have not been able to substitute frmEventReportDialog!txtBeginDate for
1/1/2007 and frmEventReportDialog!txtEndDate for 6/1/2007.
For instance,
=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >= #" &
Forms!frmEventReportDialog!txtBeginDate & "# and [EventDate] >= #" &
Forms!frmEventReportDialog!txtEndDate & "#") is one of the many
permutations of &'s and "'s I have tried.
Thank you in advance,
Claudette Hennessy

 

[Claudette Hennessy]

Thank you for your response, I got Ken Snell's solution to work. Not sure
what you mean about adding the query to the report's record source? My
original issue was the record source for the report retrieved attendance
organized differently than the result I needed for total attendance. My
solution was to add the dsum field to the report footer. Can you direct me
to a different method?
Claudette

Assuming qryDateEventAttendance isn't the record source of the report, I
would create a query like:
SELECT Sum(Attendance) as AttendSum
FROM qryDateEventAttendance
WHERE EventDate BETWEEN Forms!frmEventReportDialog!txtBeginDate AND
Forms!frmEventReportDialog!txtEndDate ;

You can then add this query which returns a single record to your report's
record source and bind the AttendSum in your report.

--
Duane Hookom
Microsoft Access MVP

I have a field in a Report Footer which should calculate a particular
Total
Attendance.

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=#1/1/2007#
and
[EventDate]<=#6/1/2007#") works fine.
However the report runs off of a dialog form which chooses the dates. I
have not been able to substitute frmEventReportDialog!txtBeginDate for
1/1/2007 and frmEventReportDialog!txtEndDate for 6/1/2007.
For instance,
=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >= #" &
Forms!frmEventReportDialog!txtBeginDate & "# and [EventDate] >= #" &
Forms!frmEventReportDialog!txtEndDate & "#") is one of the many
permutations
of &'s and "'s I have tried.
Thank you in advance,
Claudette Hennessy

 

[Duane Hookom]

The query that I suggested returns a single value/record. Your report has a
record source which is probably a query or SQL statement. You can add
multiple tables or other queries into your record source query. If the added
query returns only a single record, you don't need to join it to any other
table/query. Just add the new, single record query to your report's record
source and drag its field(s) to the grid. This makes the value available in
the report's field list.

--
Duane Hookom
Microsoft Access MVP

Thank you for your response, I got Ken Snell's solution to work. Not sure
what you mean about adding the query to the report's record source? My
original issue was the record source for the report retrieved attendance
organized differently than the result I needed for total attendance. My
solution was to add the dsum field to the report footer. Can you direct me
to a different method?
Claudette

Assuming qryDateEventAttendance isn't the record source of the report, I
would create a query like:
SELECT Sum(Attendance) as AttendSum
FROM qryDateEventAttendance
WHERE EventDate BETWEEN Forms!frmEventReportDialog!txtBeginDate AND
Forms!frmEventReportDialog!txtEndDate ;

You can then add this query which returns a single record to your report's
record source and bind the AttendSum in your report.

--
Duane Hookom
Microsoft Access MVP

I have a field in a Report Footer which should calculate a particular
Total
Attendance.

=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >=#1/1/2007#
and
[EventDate]<=#6/1/2007#") works fine.
However the report runs off of a dialog form which chooses the dates. I
have not been able to substitute frmEventReportDialog!txtBeginDate for
1/1/2007 and frmEventReportDialog!txtEndDate for 6/1/2007.
For instance,
=DSum("[Attendance]","qryDateEventAttendance","[EventDate] >= #" &
Forms!frmEventReportDialog!txtBeginDate & "# and [EventDate] >= #" &
Forms!frmEventReportDialog!txtEndDate & "#") is one of the many
permutations
of &'s and "'s I have tried.
Thank you in advance,
Claudette Hennessy