Sum in Footer of Subform

Hi ABaggott,

Thanks for posting in the community, according to your description, I did some tests trying to reproduce your situation, but I’m sorry it is little hard for me, would you mind sharing some screenshots to help us help you more efficiently?

Or you can share a simple test file with us via private message, we are always willing to offer help, and we also welcome any community members to share new ideas about your problems if one has related experiences.

Best Regards,

Arck

I'm not sure how I can send a simplified file.  The form and subforms are driven by views and tables in SQL Server.  Here are a couple of screenshots, though.  This is what the form looks like when I don't have the field summing txtSuper:  The fields in the footer of the Booths Assigned subform are sums of fields that are calculated in the view, since they only rely on data in the record itself, and a concatenation function I built to concatenate all the active booths assigned in order to compare them to the booths actually in the order when all the assignments are complete.  They are used to alert the user of discrepancies between what the sales rep has assigned and the space or booths that are actually being invoiced to the exhibitor.

As you can see, there's no error occurring in txtSub, so I know the expression used as the control source is valid.  However, when I try to sum it, using either the name of the control or the expression that works in the control, this is what I get.

I can't calculate txtSum in the view because it refers to a value in the main form.  The idea is that if there is a supersede in the most recent assignment (some of our exhibitors will change or add space multiple times over the course of the year), it will update the supersede code and supersede note in the order itself.  Any ideas?

Hi ABaggott,

Thanks for your time and patience, personally, it's too hard for me, but I’m still willing to try my best to figure out the cause, it may take some time to work on it.

I'm sorry I can't point out the cause of the problem quickly, I’ll keep you informed if there is any progress on my side, we also welcome any community members to share any ideas about this problem if one has related experience, your understanding will be highly appreciated!

Best Regards,

Arck

Actually, I ended up working around it by creating a field in my main form that used a DCount of records where either Remove or Supersede is true and the assignment date is greater than the invoice date.  If the invoice date is null (because we haven't invoiced them yet), that field is set to 0.  It seems to be working, but I'd still like to know (if anyone can figure it out) why my original idea didn't work.

Hi Amy,

Can we assume you have "=" as the first character in the control source?

You can't sum a control, only expressions based on fields and values.

IIf() returns a string variant so we often need to wrap it in Val(IIf()).

Yes, I had "=" as the first character in my control source, and the sum field in the subform footer still gave me an error.