Expression not calculating

[LizJohnson]

I have an expression on a query that is not calculating. It is for TotalFloorTaxDue.

The formula is a simple on where I tax the total of "TaxDuePacks20s"+"TaxDuePacks25s" to give me total floor tax due.

This is the expression that I have entered is TotalFloorTaxDue:Nz([tblCigaretteDataCollection]![TaxDuePacks20s],0)+Nz([tblCigaretteDataCollection]![TaxDuePacks25s],0)

When I try to put in a simple sum calculation entry of TotalFloorTaxDue: Sum([tblCigaretteDataCollection]![TaxDuePacks20s]+[tblCigaretteDataCollection]![TaxDuePacks25s]) I get an error message of "Your query does not include the specified expression 'SalesandUseTaxNumber' as part of an aggregate function." so I have discarded this expression.

There could be instances where someone may have zero in either the 20's or the 25's of the packs. Any suggestions as to why my top expression is not working?

 

[June7]

No idea. Syntax looks just fine. Except maybe don't need the table prefix.

The Sum() expression will, of course, only work in an aggregate query or in textbox on form/report in a header/footer section.

 

[ypma]

Liz Johnson, my first thought without see your query, is that perhaps the totals field of the query does not have group by for "SalesandUseTaxNumber"otherr members might have further suggestions

Regards Ypma

 

[plog]

"Your query does not include the specified expression 'SalesandUseTaxNumber' as part of an aggregate function.

My vote is with ypma. Here's a simple rule when using an aggregate function (MAX, SUM, COUNT, etc.):

When you use those functions every field in the SELECT has to be in the GROUP BY or must be included in an aggregate function (COUNT, MAX, SUM, etc).

My guess is you simply tried to add SUM to this one field without adding a GROUP BY clause. No dice. Click the Sigma/Summation symbol in the ribbon to make this an aggregate query.

 

[arnelgp]

If the first query is ok.
Create another Total query based on the first query.

 

[LizJohnson]

This is great help. This may help me with another problem that I was coming up to. I do have locations by SalesandUseTaxNumber. I do need to get a total due for the SalesandUseTaxNumber also. I have a sub-form that I am trying to get a line total due. Then I was going to try to create an expression that would then give me a "grand" total for the SalesandUseTaxNumber. I'm not very well versed in macros and functions so any help on this would be appreciated.

Is it possible to get the Total for the line item on the sub form (which is the expression I have originally asked about) and then also get the grand total?

I've tried to look up more information about the aggregate function but not having much luck with that. I'm still at a loss on how to make the line total function work. Would it not be working because the 2 items I'm trying to add together are already an expression?

 

[plog]

I have no idea what we are working on now--form, subform, query, function, expression.

You posted in the query section, so if that's what you want to work on post 2 sets of data:

A. Starting data from all relevant tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope your query will return when you feed it the data in A.

 

[LizJohnson]

Hi Plog,
So, in my query, I did put all the relevant information that I would need to come up with my expected results. I have my SalesandUseTaxNumber, LocationID, BusinessName, TobaccoLicenseNumber, Amended, Packof20s, TaxRateon20s, TaxDuePacks20s, Packof25s, TaxRateon25s, TaxDuePacks25s, TotalFloorTaxDue.

I have expressions for the TaxDuePacks20s and also TaxDuePacks25s. These two different expressions are calculating ok. The final expression I need is to add up these two TaxDue amounts. This is the expression I am using. It is not calculating the total. I don't know why?

TotalFloorTaxDue:Nz([tblCigaretteDataCollection]![TaxDuePacks20s],0)+Nz([tblCigaretteDataCollection]![TaxDuePacks25s],0)

There can be zero amounts in either Packsof20s or Packsof25s.

 

[plog]

Define, not "calculating the total". Error message? Field prompt? Incorrect results? No results? Sample data.

 

[LizJohnson]

qry73A421
SalesandUseTaxNumber LocationID BusinessName TobaccoLicenseNumber Amended Packof20s TaxRateon20s TaxDuePacks20s Packof25s TaxRateon25s TaxDuePacks25s TotalFloorTaxDue
123456 7654321 Test 2 No 3 $0.50 $1.50 5 $0.63 $3.13 0

If you will notice, the last field just has a zero when this should have a total of
$4.63

 

[Mark_]

Which of your fields are expressions? ACCESS does not play nice when you try to use the result of one expression in another expression in the same query. You will normally want ONE query to generate your calculated values, then a SECOND that works off the first to generate your second order expressions.


Example:
Query 1
ProductCost
ProductTaxRate
ProdTax: (ProductCost * ProductTaxRate)

Data in
$100 10%

Result
$100 10% $10

Query 2
ProductCost
ProductTax
TotCost: (ProductCost + Product Tax)

Result from above
$100 $10 $110

Hope this helps!

 

[June7]

1 query should be able to do the 'second order' calcs. I have done it.

 

[LizJohnson]

Thank you for your help on this. So, instead of making a 2nd query and then tying that back to the 1st query, which seems tedious, I changed my Total expression. I just made this expression mimic what was in the other two expression and adding them together. Since the expressions were so simple (tax * rate) and then adding the two, this didn't take any time. I had to delete the field and then re-add it back. I had it so confused.

Thanks for the help. By your comments, I realized that I couldn't use the fields with the expression in my final expression and had to change that.

Thanks again for your help.

 

[Mark_]

You are very welcome,

Even though you did not need it this time, please remember that you CAN use one query as the source for another. You will eventually find a case where it helps out immensely, especially if you have several other queries that are going to be driven off of the same underlying data.