Sum in Split Form

I had this question after viewing Sum column, split form.

Dear Team,
I am facing similar problem. Split form is not showing the sum and some times, it shows incorrectly. The column values are arrived using a query, jsut to clarify.
Facing similar problem in other form as well, which is similar.
Screen Shot is attached.
Thanks
2017-02-28_LI.jpg
Kamlesh JainManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
Split form is not showing the sum and some times, it shows incorrectly.
you may check the data type of that field as well as the values within that field.
Kamlesh JainManagerAuthor Commented:
data types is number and values are ok i mean numerals only.
Ryan ChongSoftware Team LeadCommented:
based on your image file in your question, can you go to the column: Sold and see what dropdown options available there?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Kamlesh JainManagerAuthor Commented:
Yes. Normal options like Count, Max, Minx, Std Deviation etc are available
2017-03-08--1-.png
Ryan ChongSoftware Team LeadCommented:
try select "Sum" and see if the value looks abnormal
Kamlesh JainManagerAuthor Commented:
tried Sum but not working... However, other functions like Count is working fine !!

Simlar to this form, i have another form but it shows vague results. Screen shot is attached, FYR.
2017-03-08--2-_LI.jpg
Ryan ChongSoftware Team LeadCommented:
are both "Lifted Qty" and "Un Lifted Qty" derived/calculated fields? can we make sure these fields returning numeric values?

possible to upload a sample (without real data) here for troubleshooting?
Kamlesh JainManagerAuthor Commented:
Sorry sir. I am not allowed to upload it on public platform. But i can send it through email, provided EE portal permissions this.
Kamlesh JainManagerAuthor Commented:
These fields "Lifted qty" and Un lifted qty are derrived by query.

Two Tables are there : tblSale and tblLifting


SELECT Val((select NZ(sum(tbllifting.[Lift_qty]),0) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN])) AS Lifted, [Sale_Qty]-Val((select NZ(sum(tbllifting.[Lift_qty]),0) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN])) AS Unlifted, *
FROM tblsale
ORDER BY tblsale.Deal_Date DESC;

  Thanks for your efforts.
Ryan ChongSoftware Team LeadCommented:
you may try:

SELECT Val((select sum(tbllifting.[Lift_qty]) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN])) AS Lifted, [Sale_Qty]-Val((select sum(tbllifting.[Lift_qty]) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN])) AS Unlifted, *
FROM tblsale
ORDER BY tblsale.Deal_Date DESC;

Open in new window


or

SELECT (select sum(tbllifting.[Lift_qty]) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN]) AS Lifted, [Sale_Qty]-(select sum(tbllifting.[Lift_qty]) from tbllifting where tbllifting.[Sale_BN] = tblsale.[Sale_BN]) AS Unlifted, *
FROM tblsale
ORDER BY tblsale.Deal_Date DESC;

Open in new window

Kamlesh JainManagerAuthor Commented:
Thanks. Tried both querries but problem is not resolved. .. will compiling the database will make difference ?

I am ok with the outcome of the data components like lifted Qty and Un lifted qty. But problem is in the sum which is inbuilt feature of split database. Can we do it manually by adding three text boxes at the bottom or top of the data sheet part ?
Ryan ChongSoftware Team LeadCommented:
will compiling the database will make difference ?
probably the answer is No.

I am ok with the outcome of the data components like lifted Qty and Un lifted qty. But problem is in the sum which is inbuilt feature of split database. Can we do it manually by adding three text boxes at the bottom or top of the data sheet part ?
I'm kinda don't get what you mean here. can you elaborate further?
Kamlesh JainManagerAuthor Commented:
I mean formula part or data outcome of the quarries are ok. I am facing problem in Sum only. So can I add three text boxes at top or bottom, and put totals of the filtered record source there ?
PatHartmanCommented:
I occasionally have a problem with the "totals" row.  It may have to do with how large the recordset is.  If you need to ensure that the row is always present and accurate, add controls to the footer of the datasheet form and copy them to unbound controls on the main form.

You can either do this with code in the AfterUpdate event of the subform or by using calculated columns.
Kamlesh JainManagerAuthor Commented:
Thanks Mr Part for your kind advise. I would like to say :

1. Recordsets in my datasheet are barely 300 records.
2. i added footer to the control of form (Its split form and Footer component doesnt show at all in Form View. :(
3. I am not using Subform as its split form in which Form Part is in Form Header and Data sheet as Details, at Bottom,
4. FYI, screen shots of both forms are  attached.

Could you help please with Totals in Text Box controls ?
Thanks in advance.
2017-03-08--1-.png
2017-03-08--2-_LI.jpg
PatHartmanCommented:
The totals feature is attractive on the surface but I have also found it to be unreliable  The problem you are experiencing might be caused because you are using a split form.  That form stile is easy to create the old-fashioned way so you don't need to use it either especially since it is also prone to strange errors.  

What you are seeing is a bug.  None of us can fix this type of bug.  That is why I offered an alternative.  If you want to contact Microsoft, they might fix the problem for you.  Be prepared to front them around $500.  They will look at the problem and offer solutions.  If the problem is caused by a bug, they will refund your fee.  None of the free help will look at your code or even be competent in Access.  The free help is geared more to the other Office products and typical user problems.  They can't help with programming problems.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kamlesh JainManagerAuthor Commented:
Thanks team. Now I will try to convert into subform or continuous form. Thanks for your efforts and time devoted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.