Control Source unbound Text Box Error 2

[cneill]

This is driving me mad

Checked all the nameing rules, so no problem as far as I can see

I have a unbound text box call SumDel in the Forms Header
in the Control Source I have
=iif([SchemeRef]=52,Sum([Delivered]))
SchemeRef and Delivered are bound text boxes in the forms Detail section being fed from the Forms query.
This results in an #Error
I have also tried
=Sum(iif([SchemeRef]=52,[Delivered]))
=Sum(NZ(IIf([SchemeID]=52,[Delivered])))
Again same result #Error

I have also tried
=[Delivered]
=[SchemeRef]
These work they return the value in the first row
Does any one have any further ideas?
Thanks CNEILL

 

[PHV]

I'd try this:
=Sum(IIf([SchemeRef]=52,[Delivered],0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bronc]

not sure why you need the Sum. esp if its a text field?

I guess the main thing is that Iff takes 3 arguments not 2.

 

[cneill]

Hi Everyone,

=Sum(IIf([SchemeRef]=52,[Delivered],0)) also returns #error

any further thoughts?

Thanks CNEILL

 

[bronc]

Does =IIf([SchemeRef]=52,[Delivered],0)
work?

how about

=IIf([SchemeRef]=52,[Delivered],"")

?(not sure what Type of data [Delivered] is.)

how about

=IIf(-1,"dummy","")

=IIf(0,[Delivered],"")

? etc etc to try to isolate the error.

 

[dhookom]

Why not begin by telling us the data types of [SchemeRef] and [Delivered]?
You also stated

SchemeRef and Delivered are bound text boxes

Are they actually numeric fields in your form's record source?

=Sum(Abs([SchemeRef]=52) *[Delivered])

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi Everyone,

Bronc, I will try your ideas

dhookom,
Delivered is the quantity of items delivered so this is a numeric field so that I can calculate the Total

SchemeRef - I sould have said this is SchemeRefID which is a forign key, so 52 is linked to a table so that I can collect the name of the SchemeRefID, in this case 52 is TVPowerDowns

So the Control Source is basically looking for all the 52 SchemeRefID's then summing the the quantity in the Delivered field

 

[cneill]

bronc

=IIf([SchemeRef]=52,[Delivered],0)
This works but only collects the first row value on the form, I need to sum all of them with the 52 SchemeRef

 

[MajP]

how about an aggregate function?
=dsum("Delivered","SomeQuery","SchemeRef = 52")

 

[cneill]

hI Majp,

Yes this works but then this causes me a different problem.

The Forms Query can be filtered useing the following
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long Const conJetDate = "\#mm\/dd\/yyyy\#" DoCmd.Hourglass True
If Not IsNull(Me.txtFilterSchemeRef) Then
strWhere = strWhere & "([SchemeRefID] Like ""*" & Me.txtFilterSchemeRef & "*"") AND "
End If

Various other filter options..........

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "You have not Entered any Search Criteria, Please Enter some information to Search for!", vbInformation, "Just to let you know"
Exit Sub
End If

If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If

DoCmd.Hourglass False
End Sub

So by using this Dsum it will not re filter, any thoughts?

 

[MajP]

Well someone asked recently if there was a way to use a dsum on the forms recordset. This is what I built.

Public Function dSumRecordset(Expression As String, RecSet As DAO.Recordset, Optional Criteria As String = "") As long
  On Error GoTo errlbl
  If Not Criteria = "" Then
    RecSet.Filter = Criteria
    Set RecSet = RecSet.OpenRecordset
  End If
  Do While Not RecSet.EOF
     dSumRecordset = dSumRecordset + (Nz(RecSet.Fields(Expression), 0))
     RecSet.MoveNext
  Loop
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

Then on the form I make a function to call this function. It is easier than calling it from a calculated control

Public Function getDsumOrder() As Long
  getDsumOrder = dSumRecordset("UnitsOnOrder", Me.Recordset.Clone)
End Function

Now I can change the recordset and filter the recordset.

 

[MajP]

The original approach you were using should work, but this function does come in handy.

 

[cneill]

HI MajP,

This is really good and works well, but there is more than one SchemeRefID, I would like to show all the SchemeRefID's with their Delivered Totals possibly in a List box, so there would be seperate Delivered totals for each SchemeRefID.
Could the code be modified to filter/split by SchemeRefID?

 

[MajP]

Sounds to me you would want to simply build the listbox using an aggregate query. You can build a simple query that shows each sum by schemarefID.

 

[MajP]

Something like:
SELECT
SchemaRefID,
Sum(Delivered) AS TotalDelivered
FROM
YourTableName
GROUP BY
SchemaRefID

 

[cneill]

Hi MajP,

Yep Done, but this is where I have a bit of a grey area, how do I re calulate the Delivered Totals in the List box when I filter the form.
I put =getDsumOrder() in the List Box Control Source but that does not work, so what am I doing wrong?

 

[MajP]

I think the easiest would be something like this untested.

dim strSql as string
dim strWhere as string

strWhere = me.filter
'Build the listbox sql
strSql = "SELECT SchemaRefID, Sum(Delivered) AS
TotalDelivered FROM YourTableName"
'stick the filter in the sql
strSql = strSql & " WHERE" & strWhere
strSql = strSql & "GROUP BY SchemaRefID"
'reset the listbox filter
me.someListBox.rowsource = strSql

 

[cneill]

Hi MajP,

I think we are nearly there, okay what is happening is as follows
When form opens it populates the List box with all the SchemeRefID's and the total Delivered, which is great,
Now if I select SchemeRefID 52 then use the button to run
Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterSchemeRef) Then
strWhere = strWhere & "([SchemeRefID] Like ""*" & Me.txtFilterSchemeRef & "*"") AND "
End If

This filters the form so the form only shows the records from the SchemeRefID I have selected.

Now I added the new code you gave me at the end of the Private Sub cmdFilter_Click() code so that it runs this part as well after the form has been filtered, it works but shows all the SchemeRefID's with their totals, it will not filter the single SchemeRefID that I selected.
Any Thoughts?

 

[MajP]

I tested the above and if you generate the strWhere it will work. If you read the filter from the form
strWhere = Me.Filter
it might not work. If you use the access form filter it may add an _ to the table name in the filter. I do not know why. You may be able to then remove it
'strWhere = Replace(strWhere, "_", "")
But a lot of people use underscores in their table name. Should not be an issue for you because you build the strWhere.

 

[TheAceMan1]

Howdy cneill . . .

I wanted to break-in earlier ... but the flow of this thread was prohibitive. The suggestion I origionally conceived was:

[blue]=Sum(IIf(Nz([SchemeRef],0)=52,Nz([Delivered]),0))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]