Sum of a field defined by query expression

[age0004]

I am having a hard time getting a sum without an #error.

I have a query named Qry_orderdetails. Within it there is a field called ExtendedPrice, where I have an expression as ...

ExtendedPrice: ([Quantityordered]*[vpPrice])

The form for this query is in datasheet view and is being used as a subform on the main form called OrderForm. I have already put an unbound textbox in the footer of the subform and tried =Sum([extendedprice]). I got it to work one time, changed absolutely nothing, then it gave me the #error. I remade the query, re-did the calculation and expressions etc, then the sum worked...and again changed nothing and soon after it quit working again.

Is this just a terrible way to go about getting a sum?? I dont understand why it would work and then suddenly stop working.

Anyone have any ideas or suggestions?

 

[theDBguy]

Hi. For some reasons I can't remember, the Sum() function is a little sensitive. When other functions have problems, the Sum() function empathizes and refuses to work as well. If this is the case, you need to isolate the problem by removing the Sum() function first and make everything is working. If not, then take out other functions one at a time or fix whichever function you found out was not working. It happened to me before when I had multiple Sum() on a form. When one of them broke, all the rest broke as well. Hope it helps...

 

[Deleted member 147267]

Intermittent #Error in a form field can be caused by modifying a form and switching to form view from design without saving first.

EDIT: On 2nd thought, I might be remembering this behaviour resulting in a #Name error...

 

[The_Doc_Man]

First thing to do would be to open the query that shows you "ExtendedPrice" and verify that you have no nulls or #error in THAT column. Because what is going on is that some error is propagating from either [Quantityordered] or [vpPrice]. Something is breaking one of them. Find the culprit and you can fix it.

Is there perhaps an OUTER JOIN (LEFT or RIGHT JOIN) involved in the defining query such that you might encounter a null on some occasions?

 

[age0004]

First thing to do would be to open the query that shows you "ExtendedPrice" and verify that you have no nulls or #error in THAT column. Because what is going on is that some error is propagating from either [Quantityordered] or [vpPrice]. Something is breaking one of them. Find the culprit and you can fix it.

Is there perhaps an OUTER JOIN (LEFT or RIGHT JOIN) involved in the defining query such that you might encounter a null on some occasions?

So when I open up the query all of the fields are working correctly with no nulls or #errors.
As far as the OUTER JOIN (LEFT or RIGHT JOIN) im not quite sure if I know what that is? If its too complicated to explain ill do some research to teach myself about it.
Still receiving errors even after making sure the query is working properly.

 

[The_Doc_Man]

You would know if you were using one, simply by taking your query and momentarily switching it to SQL view. The word JOIN would be present in the FROM clause and would be preceded by either INNER, LEFT, or RIGHT - even if you had used the query grid (design view) to define your query, as long as an appropriate relationship was defined.

 

[age0004]

Okay, thank you. I copied and pasted the SQL below

SELECT OrderDETAILS.OrderID, OrderDETAILS.OrderDetailID, OrderDETAILS.QuantityOrdered, OrderDETAILS.vpID, OrderDETAILS.ShippingCost, OrderDETAILS.TrackingNumber, OrderDETAILS.ExpectedReceivedDate, OrderDETAILS.Paid, OrderDETAILS.ReceivedDate, [QuantityOrdered]*[vpPrice] AS ExtendedPrice, EquipmentDETAILS.vpPrice, EquipmentDETAILS.vpPartNumber, EquipmentDETAILS.vpPartDescriptionID
FROM OrderDETAILS INNER JOIN EquipmentDETAILS ON OrderDETAILS.vpID = EquipmentDETAILS.vpID;


So I guess I did and did not know it!

 

[Mark_]

Doc,

Thank's for the info. This explains why I got in a habit that works for me. I never do totaling off of large queries, I normally make small ones that I can verify easily. Didn't realize joins could be why I had been having problems in the past!

 

[age0004]

Doc,

Thank's for the info. This explains why I got in a habit that works for me. I never do totaling off of large queries, I normally make small ones that I can verify easily. Didn't realize joins could be why I had been having problems in the past!

So do you have any advice or tips for how I can fix this?

 

[The_Doc_Man]

age0004 - if you had a JOIN and didn't know it, that implies you built that query using either a query wizard or the query design grid AND that you had defined a formal relationship between the joining tables. Not an unusual situation for folks unused to Access to find that they had used some of the smarts behind the scenes. AND not a wrong thing to do, particularly for folks still learning some of the vagaries of the Access development environment.

HOWEVER, in this case, that information doesn't help. The reason I asked about JOIN at all is because if you had a LEFT or RIGHT join, you MIGHT sometimes expect records with nulls in cases where a "parent" record didn't have a "child" record. Nulls might negatively affect other records and that negativity could creep into the SUM() output. But an INNER JOIN doesn't do that so the simplest reason doesn't seem to apply.

Is either OrderDETAILS or EquipmentDETAILS based on a query? Or perhaps on a non-standard linkage, like maybe one of them is a spreadsheet linked as though it was a table? I'm looking for things that will interfere with the flow of data. Your query looks pretty much as straight-forward as it gets. The JOIN is the only wrinkle and that appears to be validly formed as well. Therefore, the cause of your indicated problem hasn't been seen yet. This layer looks good. Time to peel back the onion.

Talk to us about the data source tables, 'cause that's the next layer.

 

[Mark_]

What tables are [QuantityOrdered] and [vpPrice] coming from? I'd make sure that neither is null nor 0 first.

I'd have them explicitly listed in your query THEN toss in your expression. This lets you check for bad data.

 

[age0004]

age0004 - if you had a JOIN and didn't know it, that implies you built that query using either a query wizard or the query design grid AND that you had defined a formal relationship between the joining tables. Not an unusual situation for folks unused to Access to find that they had used some of the smarts behind the scenes. AND not a wrong thing to do, particularly for folks still learning some of the vagaries of the Access development environment.

HOWEVER, in this case, that information doesn't help. The reason I asked about JOIN at all is because if you had a LEFT or RIGHT join, you MIGHT sometimes expect records with nulls in cases where a "parent" record didn't have a "child" record. Nulls might negatively affect other records and that negativity could creep into the SUM() output. But an INNER JOIN doesn't do that so the simplest reason doesn't seem to apply.

Is either OrderDETAILS or EquipmentDETAILS based on a query? Or perhaps on a non-standard linkage, like maybe one of them is a spreadsheet linked as though it was a table? I'm looking for things that will interfere with the flow of data. Your query looks pretty much as straight-forward as it gets. The JOIN is the only wrinkle and that appears to be validly formed as well. Therefore, the cause of your indicated problem hasn't been seen yet. This layer looks good. Time to peel back the onion.

Talk to us about the data source tables, 'cause that's the next layer.

So, I have four tables involved in this.
1) Equipment (only holding the description of the part)
2) EquipmentDETAILS (holding all different vendors/prices for each of the parts)
3) Orders (only holding customer, job name, etc)
4) OrderDETAILS (holding information about all of the things that was ordered for that customer/job)

Qry_orderdetails is just based from the table OrderDETAILS. I was advised a while back to not use formulas in the table for a field but to make a query with a formula in it. Here are some images....


Capture 1 is Orders
Capture 2 is OrderDETAILS
Capture 4 is EquipmentDETAILS
Capture 5 is Equipment
Capture 6 is Qry_orderdetails

 

[The_Doc_Man]

I was advised a while back to not use formulas in the table for a field but to make a query with a formula in it.

Whoever told you that was your friend. That's excellent advice. There is no hard-and-fast "NEVER" case but there are quite a few "HARDLY EVER" caes. Tables are for data. Queries are for manipulation.

On to your problem:

Looking at your "DETAILS" tables, one question jumps out at me. You showed price with dollar-signs. How do you achieve the dollar signs? If that was a currency field, then Access will present it for display with currency symbols, but if that is actual text, i.e. <dollar-sign><digit><digit><dot><digit><digit> in the field (because it was text format) then you might have a problem. What is the data type of the vpPrice field?

If that isn't the issue, there is not much left. The last possibility would be a partial or complete corruption of the back end database. There are two levels of fixing this.

Level one is to isolate the back-end so that nobody is in it or any of the associated front ends, then make a backup copy (for safety in case everything goes whackadoodle on you). Then do a compact and repair. See if the problem persists.

Level two is to make a new, blank database and then import structure and data (and relationships) for all tables from the old back-end. (Of course, plus anything else that was in the back end.) Then rename the old database to xxxx_OLD.accdb (or .MDB) and rename the new one to the name you used when you originally linked the FE to the BE. The "permanent" links will automatically re-establish by name, not by internal file ID, so if you get the names right, you won't notice the difference.

 

[age0004]

So the field is set to currency, without any "text dollar signs" in the field. This is the case for all fields that are currency...

As far as this database is concerned I have not split it yet. Its fairly small still so I was going to wait until I fixed this problem (the last problem I have actually) until I will split it. I practiced splitting an older version of this database and it worked fine so I feel comfortable doing that. I just need to fix this one little issue before I can make this "user ready"

 

[The_Doc_Man]

It is not uncommon to see a currency symbol output from a Currency data type so that is no particular surprise.

My next guess would be to take your query that contains the computation and to separately display the individual fields contributing to that computation. Because on the ones that show #Error in the computation, you should also see #Error in one of the contributors.

So for the query you showed in post #7, you have not only your extended price but also your contributors, QuantityOrdered and vpPrice. Do you see #Error for one of them when you see the #Error in the ExtendedPrice?

If you do, the problem is in the contributor that was showing an #Error - BUT if you see fields A, B, and A*B, but ONLY field A*B shows an error and at the same time, neither A nor B shows an error, then you have some kind of obscure corruption.

 

[age0004]

Okay, I see what you are saying. In this case, however, all of the fields are working correctly. The only field with the error is the subtotal. The extended price calculates correctly and the prices for all of the parts appear to not have any errors as well.

So the Capture1232 is the form based from the QRY_orderdetails viewed in "form view" now... this form is a subform that will always be in datasheet view, thats why it looks like trash.

The Capture12 is the same form but in design view so you can see what everything is...

and Capture 2123 is the query in datasheet view to show that everything is working correctly.

One thing I just noticed is that the vpPrice coming from the vpID column (4) is set to currency format but not showing up with a "$" so Im not sure if thats causing any issues or not.

 

[The_Doc_Man]

OK, I took another look at post #1 and I noticed something that I thought MIGHT (repeat MIGHT) help.

The form for this query is in datasheet view and is being used as a subform on the main form called OrderForm. I have already put an unbound textbox in the footer of the subform and tried =Sum([extendedprice]). I got it to work one time, changed absolutely nothing, then it gave me the #error. I remade the query, re-did the calculation and expressions etc, then the sum worked...and again changed nothing and soon after it quit working again.

Change =Sum([extendedprice]) to =DSum("[ExtendedPrice]", "Query_OrderDetails"). See if that is more stable. If it is, then the problem had to do with the way you defined those unbound text boxes for the sums. If there is a filter to be involved later, it can be added to the DSum as easily as it could be added to the form or sub-form.

Just remember that sometimes what looks like "grasping at straws" can also be thought of as a "process of elimination."

 

[gemma-the-husky]

I suspect you need
sum([Quantityordered]*[vpPrice])

rather than
sum([ExtendedPrice])

The second way might work if you have a query, and then use another query including the first query results.

I too find it a pain, when my first excperession is somewhat complicated, but I can't just sum the result.

 

[age0004]

OK, I took another look at post #1 and I noticed something that I thought MIGHT (repeat MIGHT) help.



Change =Sum([extendedprice]) to =DSum("[ExtendedPrice]", "Query_OrderDetails"). See if that is more stable. If it is, then the problem had to do with the way you defined those unbound text boxes for the sums. If there is a filter to be involved later, it can be added to the DSum as easily as it could be added to the form or sub-form.

Just remember that sometimes what looks like "grasping at straws" can also be thought of as a "process of elimination."

OH MY GOODNESS! Thank you, thank you, thank you! DSum works!!!! So thankful for all of your help. I hope it keeps working and doesnt act funny later on, but the fact that Im getting a sum now is amazing. Thank you!!

 

[The_Doc_Man]

OK, let's keep our fingers crossed (though it is DAMNED hard to touch-type that way...)

If =Sum(...) doesn't work and =DSum(...) does, then the problem was that the "scope" of the Sum() is inconsistent. Of course, we have to see if DSum() stays consistent.

Here's a hint for future consideration. If you ever decide to apply a filter to the form, just remember that the filter clause can ALSO be copied and used as the criteria clause (3rd argument) in the DSum(), as long as the form uses the same table as is used in the domain (2nd argument) in the DSum().