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 -
HST: IIf([PettyCashdetailsT].[taxExempt]=Yes,0,[pettyCashDetailsT].[Amount]*0.12)
-
-
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
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
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? 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.
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?
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?
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
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.
Thank you for your Time on answering my question neopa.
cheers
jaad
@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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |