Avatar of Kamlesh Jain
Kamlesh Jain
Flag for India asked on

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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Kamlesh Jain

8/22/2022 - Mon
Ryan Chong

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 Jain

ASKER
data types is number and values are ok i mean numerals only.
Ryan Chong

based on your image file in your question, can you go to the column: Sold and see what dropdown options available there?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kamlesh Jain

ASKER
Yes. Normal options like Count, Max, Minx, Std Deviation etc are available
2017-03-08--1-.png
Ryan Chong

try select "Sum" and see if the value looks abnormal
Kamlesh Jain

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

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 Jain

ASKER
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 Jain

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Kamlesh Jain

ASKER
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 Chong

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 Jain

ASKER
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 ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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 Jain

ASKER
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
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kamlesh Jain

ASKER
Thanks team. Now I will try to convert into subform or continuous form. Thanks for your efforts and time devoted.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy