Prompt to enter paramater value for TankDate pops up againOkay, now, try switching the expression back to the original, but keep the Textbox in the Details section still.
Prompt to enter paramater value for TankDate pops up againOkay, now, try switching the expression back to the original, but keep the Textbox in the Details section still.
Ok...that will be 9pm over here so I will wait for your help as I really want to get this report completed tonight.For that short period of time, I won't be able to see it, since I am only using my phone right now. Hopefully, somebody else will be able to. Otherwise, I'll be back in front of a computer in about two hours.
Sounds like your control's name is the same as the field's name. Try changing the name of the control.Prompt to enter paramater value for TankDate pops up again
No. I was referring to the Textbox for TotalTanks. Is it also on the report, and the names are different?No no...different names. I haven't even named the calculated field in the group footer - still the generic TextNo
In the details section I have highlighted the [TotalBases] and [TotalTanks] which are what I need to sum in the group footer
Hmm, if TotalTanks is not in the Record Source for the report, then that's probably why you were getting prompted for it. Try adding it to the record source.The TotalTanks textbox is in the report only. It is not on any form or in the source query.
Due to the info I need to show in this report there are quite a few Dlookup fields to get the activity dates.
So not ALL candidates.
Consider this query as report RecordSource.
SELECT tblCandidates.CandidateID, tblCandidates.Name, tblCandidates.Address, tblParishes.Parish, tblCandidates.ContactInfo1, tblCandidates.ContactInfo2, tblCandidates.ContactInfo3, tblCandidates.Notes, tblCategories.Category, tblCandidates.System, tblWorkOrder.PreInspectionDate, tblWorkOrder.BaseSubstructureDate, tblWorkOrder.BaseInstallationDate, tblWorkOrder.TankInstallationDate, tblWorkOrder.PlumbingConnectionsDate, tblWorkOrder.ElectricalConnectionsDate, tblWorkOrder.InspectedOn_IPS, tblWorkOrder.CommissionDate
FROM tblParishes RIGHT JOIN (tblCategories RIGHT JOIN (tblCandidates LEFT JOIN tblWorkOrder ON tblCandidates.CandidateID = tblWorkOrder.CandidateID) ON tblCategories.CategoryID = tblCandidates.CategoryID) ON tblParishes.ParishID = tblCandidates.ParishID;
Can include WHERE clause to filter on Remove or apply filter when report opens.
Now the count on System multi-value field can be done with DCount(). I tried using Count() and it errors.
=DCount("System.Value","tblCandidates","CandidateID=" & [CandidateID])
Could even do that DCount() in query so it is then a field that can be referenced with Sum() expression on report.
Another approach would be a query that expands the multi-value field elements to separate rows. Then can use Count() and Sum() expressions on the expanded dataset. Set textbox HideDuplicates property to Yes.
Report is too wide for page size. Need to adjust width of some textboxes.
Thank you again for your help! This is counting the tanks per system as I needed. I can now use this for the next week's reports.It doesn't work because the actual value saved in the multi-value field is a number - the ID from tblSystems. Let's take a different approach. Build the following query object:
qryCandidatesTankCount
SELECT tblCandidates.CandidateID, Sum(IIf(InStr([tblSystems].[System],"1000")>0,2,1)) AS TankCount
FROM tblSystems RIGHT JOIN tblCandidates ON tblSystems.SystemID = tblCandidates.System.Value
GROUP BY tblCandidates.CandidateID;
Now just include that query like a table in the report RecordSource joining on CandidateID fields. Bind textbox to TankCount field.
No domain aggregate functions.