473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

calculating the sum of a calculated expression field from a subform

158 100+
Hello all,

I have a petty cash main form: PettyCashTF
and a supporting sub form: PettyCashDetailsTF

to keep track of reimbursable expenses.

the main form keeps track of the date, vendor, invoice number and so on.

The subform keeps track of the details for the transaction:

Item purchased
Quantity
Unit price
tax exempt (check box)
note

I've added two calculated field to automatically calculate HST(sales tax) and the [total]in the subform

Expand|Select|Wrap|Line Numbers
  1. HST: IIf([PettyCashdetailsT].[taxExempt]=Yes,0,[pettyCashDetailsT].[Amount]*0.12)
  2.  
  3. Total: ([Amount]+[HST])*[quantity]
this works fine.

what I would like is to get the sum of the calculated field [total] from my subform and show it into the mainform.

I can't seem to be able to do this. any idea how I can manage to do this?

thanks
jaad
May 21 '11 #1

✓ answered by NeoPa

You're not wasting my time Jaad. It seems you aren't following what I've posted though. You asked "Is it possible to sum a control?" The answer is "No." Whatever is in it - IIf() or anything else - the answer is still no.

However, there is a way around that, which I also explained in my post (#6). This way will work with controls that include IIf() too.

10 5376
NeoPa
32,556 Expert Mod 16PB
Referring to Items on a Sub-Form should help with that.
May 22 '11 #2
jaad
158 100+
referencing the subform is not my problem. the problem that I have is because the HST field is an aggregate function and access doesn't let me group it because of that. or am I'm not getting something?

Attached Images
File Type: jpg pettycash.jpg (45.5 KB, 2477 views)
May 22 '11 #3
NeoPa
32,556 Expert Mod 16PB
Are you talking about fields or controls. You say fields, but if they were fields I would expect them to work.

We are talking about Access here. Recordset Fields <> Object Controls.
May 22 '11 #4
jaad
158 100+
control I supposue. the values in the control name [total] is an expression that calculate to cost of the item + HST Tax. is there a way to culculate on form the sum of all [total]. I can do it on a report, just asking if i can also do it on a form?
May 22 '11 #5
NeoPa
32,556 Expert Mod 16PB
Jaad:
control I suppose.
Well, this is something you should be entirely sure of, because the question doesn't make sense unless this is clear. This is too important to suppose.

In answer to your question though, essentially "No". Controls cannot be summed in the way you require. Only fields can be used that way (This is also true for Reports BTW. You are mistaken if you suppose otherwise).

However, you can probably handle the situation by using Sum() around the same formula as used in the control you want to sum. So, if the detail control (Me.txtDtl) formula is =[Field] * 2 + 50 then you couldn't say =Sum([txtDtl), but you could say =Sum([Field] * 2 + 50) instead, which would work. Does that make sense?
May 22 '11 #6
jaad
158 100+
i understand what you mean. but if you look above on my first post you will see that the HST control has a (iif) statement in the expression and access wont let me sum it because of that. i will try to figure out another way of showing the sum, maybe just create a button to open the related report where I can see the sum.
it's not a big deal and don't want to waste your time with this. thanks a bunch.

jaad
May 23 '11 #7
NeoPa
32,556 Expert Mod 16PB
You're not wasting my time Jaad. It seems you aren't following what I've posted though. You asked "Is it possible to sum a control?" The answer is "No." Whatever is in it - IIf() or anything else - the answer is still no.

However, there is a way around that, which I also explained in my post (#6). This way will work with controls that include IIf() too.
May 24 '11 #8
jaad
158 100+
Thank you for your Time on answering my question neopa.

cheers
jaad
May 24 '11 #9
@NeoPa
Thanks NeoPa, I'm having the exact same issue, and I have already tried exactly what you suggest, but still the result is just a blank field. My particulars:See attached form in design view (pic2) and results (pic2). Everything works just fine, except the sales tax field does not sum (or at least display anything...no error message appears. Confused!
Attached Images
File Type: jpg LH2pic1.jpg (32.6 KB, 277 views)
File Type: jpg LH2pic2.jpg (18.5 KB, 289 views)
Sep 16 '16 #10
NeoPa
32,556 Expert Mod 16PB
You'll need to ask your own question separately Rass. Posting a follow-up in here is pointless. It's just a hijack.

Feel free to post a link to this thread in your new thread if you like, and even a link to your new thread in here if it helps, but a follow-up in here is pointless.
Sep 17 '16 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
2
by: david | last post by:
Hi, I have a form with a couple of calculated fields. I want to put some code into the 'Form-Load' event to set various object states on the form, depending on the value of these fields. The...
30
by: Shannan Casteel via AccessMonster.com | last post by:
I have a subform named "sbfrmParts" with a list of parts along with the quantity and price. I have used a text box in the subform's footer and set the control source to "=Sum(*)". I set the...
0
by: trishbarker | last post by:
I've created a simple select query in Microsoft Access wherein I'm searching all records based on the "Final Payment Due Date". The Final Payment Due Date is a calculated expression based on two...
9
by: Alan Mailer | last post by:
Ok, my Access 2002 language writing skills are VERY rusty,. I would know how to do what I need using SQL Server's "Coalesce' function, but I don't have that available to me in the Access 2002...
11
Dököll
by: Dököll | last post by:
Hello Hello! Hope it's a good week-end near you... I am trying to substract then divide through a form field to no avail. What do you make of it? (1) Achieved form grabs a certain number...
6
by: Accessnewb | last post by:
I'm trying to calculate 2 seperate fields in a subform First one is TotalQuantity I have =Sum() in the subform footer I then show this total on the main form with =Windows.Form!TotalQuantity...
0
by: Tara Small | last post by:
I am tring to create a form in which a expiration date and pre-expiration reminder dates are calculated from a user defined certification date. I then want this calculated expiration field to show...
1
by: lvmoore | last post by:
I have the calculation located in the footer of the subform. There is sometimes no rows in this form so I have used Allen Browne's IIF(FormHasData(),calculation,0) syntax to show a zero if there...
2
by: Daryl Austin | last post by:
I am new to Access and need some help with creating calculated fields. I am trying to create a calculated sum field in a form, the sumfield would add the values of 5 fields in a table (all 5 fields...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.