Thanks, Mark. That does help to clarify what you are doing.
I assume that Postings is a Number field, and you say you enter a count for
each date (based on the DateIssue date, I assume.)
I'm not clear why we have a Postings group header. If you did that just to
fix the problem, delete that row from the Sorting And Grouping dialog.
Instead, set the Group Header property to Yes for DateIssue. And move the
txtPostingsRS text box into this section. You can set this section's Visible
property to No if you don't want it to print.
This will give a running sum in the header of each date. It will therefore
accumulate the correct total (one value for each date, and not the repeating
value from the Detail section), which should give you the correct total in
the end.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Mark Jackson said:
Here is the SQL for my query:
PARAMETERS [Enter Begin Date] DateTime,
[Enter End Date] DateTime;
SELECT Postings.Postings,
Gateway.NIIN,
Solicitations.LongTerm,
Solicitations.SmallBusiness,
Solicitations.Center,
Solicitations.InitialDrawing,
Solicitations.Quantity,
Solicitations.LastAwardPrice,
Solicitations.DateIssue,
Solicitations.DateDue,
Solicitations.QuoteStatus,
Solicitations.Notes
FROM Postings INNER JOIN (Gateway INNER JOIN Solicitations
ON Gateway.ContactID = Solicitations.ContactID)
ON Postings.Date = Solicitations.DateIssue
GROUP BY Postings.Postings,
Gateway.NIIN,
Solicitations.LongTerm,
Solicitations.SmallBusiness,
Solicitations.Center,
Solicitations.InitialDrawing,
Solicitations.Quantity,
Solicitations.LastAwardPrice,
Solicitations.DateIssue,
Solicitations.DateDue,
Solicitations.QuoteStatus,
Solicitations.Notes
HAVING ((Solicitations.DateIssue >= [Enter Begin Date])
And (Solicitations.DateIssue < ([Enter End Date] + 1)))
ORDER BY Solicitations.DateIssue;
Here is the Sorting and Grouping info on my report:
Field/Expression
DateIssue - Group Header = No
Postings - Group Header = Yes
The field that is not working right is PostingTotal which is located in
the
Report Footer. The control source is =[txtPostingsRS]. The total
postings
for each day which is located in the detail section is correct but when I
try
to total all of the days it only gives me the total of the last day I
entered. I hope this is enough information. Thanks for your help.
Allen Browne said:
Mark, we've covered quite a range of territority in this thread,
addressing
several problems. I'm not clear where you are up to now.
Switch the query to SQL View (View menu in query design).
Copy the SQL statement and paste it here.
Then open the report in design view.
Open the Sorting And Grouping dialog (View menu.)
List the rows in this dialog, and indicate which ones have Yes beside the
Group Header and Group Footer properties.
Now, in which section is the text box that fails to give the right
answer?
And what is is Control Source property?
It only totals the last day's entry. I would like it to total the date
range
I enter in. If I enter more than one day it will only total the last
entry.
Any suggestions?
Thanks.
:
To ensure Access understands them correctly, declare the parameters.
Choose
Parameters on the Query menu, and enter 2 rows in the dialog:
[Enter Begin Date] Date/Time
[Enter End Date] Date/Time
Now change the criteria to:
= [Enter Begin Date] And < ([Enter End Date] + 1)
That should ensure any time values on the final date are included.
message
I have Between [Enter Begin Date] And [Enter End Date] in the
criteria
field
in DateIssue on my query. That is how I get my date range.
:
Perhaps you have a time component as well as a date in that field?
Try < #11/3/2006# instead of <= #11/2/2006#
Alternatively, it is possible that Access is not recognising the
data
types
correctly (e.g. it is performing a string comparison instead of a
date
comparision.)
message
Hi Allen,
There was an extra space in the name property. I not getting the
error
anymore but the total is not correct. I ran my report for
11/1/06
to
11/2/06
and I should have a total of 43 postings, instead I have 20. I
noticed
the
20 is the last day's entry. On November 1 there were 23 postings
and 2
kits
I could work on and on November 2 there were 20 postings and 1
kit I
can
work
on. Any idea's? Your help is appreciated.
Mark
:
If Access pops up a parameter dialog, it means Access can't find
the
object
with that name.
Check the Name property of the text box is set correctly. It
must
match
exactly (e.g. it cannot have an extra space.)
Also, check that the controls are in the correct sections (e.g.
not
in
the
Page Footer section.)
message
Thank you for your reply. I tried what you posted and it does
not
work.
When I view my report a box pops up saying
"Enter Parameter Value"
txtPostingsRS
When I hit ok, it shows my report but no total in the report
footer.
Any
help is appreciated. Thanks
Mark
:
Create a group header for Postings.
(Sorting And Grouping box, on the View menu.)
In this section, add a control with these properties:
Control Source =[Postings]
Running Sum Over All
Visible No
Name txtPostingsRS
Now add another text box to the Report Footer section, and
set
its
Control
Source to:
=[txtPostingsRS]
The Running Sum property causes it to accumulate the value
once
for
the
grouping. The final text box then displays the correct total.
in
message
I have a report that is bound to a query. In this query I
am
using
3
different tables. Kit, Solicitations, and Postings. In
the
Kit
table
I
have
stock number, part number, and description. In
Solicitations
I
have
Date
Issued, Due Date, Solicitaion Number, and Price. And in
Postings
I
have
Postings and Date. Each day I enter the total number of
Postings
available
for that day and any Kits we can quote on. Sometimes on
any
given
day
I
can
have 20 kits that we can quote on. The problem I am having
is
when
I
run
my
report I am not getting the correct sum of Postings. I
noticed
on
my
report
it shows the number of Postings for each Kit entry for that
day.
I
have
set
the hide duplicate to yes which hides all the Postings
except
one
for
that
day but when I sum the Postings field it totals all the
duplicate
too.
Is
there anyway to sum only ones that are not duplicates? Any
help
is
appreciated.
