Building expression problem using Access Query.

[Guest]

I'm still a bit new to building expressions in Access. I've been working in
a query, on sales data.

The sales date table has "Date", "Quantity", "Taxable Items Price",
"NonTaxable Items Price".

I've created subtotals by creating the following expressions:
Subtotal Taxable: ([Quantity]*[Taxable Items Price])
Subtotal Nontaxable: ([Quantity]*[NonTaxable Items Price])
These both work.

But when I try to create a new column to get a total to use in another
place, neither of the following expressions work:
Total Sales: ([Subtotal Taxable]+[Subtotal Nontaxable])
Total Sales: ((Quantity]*[Taxable Items Price]+[Quantity]*NonTaxable Items
Price])

What am I doing wrong, or what should I be doing?

(Using trial version of Office Pro 2007)

Thanks for any help you can provide.

 

[John W. Vinson]

I'm still a bit new to building expressions in Access. I've been working in
a query, on sales data.

The sales date table has "Date", "Quantity", "Taxable Items Price",
"NonTaxable Items Price".

I've created subtotals by creating the following expressions:
Subtotal Taxable: ([Quantity]*[Taxable Items Price])
Subtotal Nontaxable: ([Quantity]*[NonTaxable Items Price])
These both work.

But when I try to create a new column to get a total to use in another
place, neither of the following expressions work:
Total Sales: ([Subtotal Taxable]+[Subtotal Nontaxable])
Total Sales: ((Quantity]*[Taxable Items Price]+[Quantity]*NonTaxable Items
Price])

What am I doing wrong, or what should I be doing?

You can't usually use a calculated expression in a further calculation: you
need to recapitulate the calculation. In addition, you seem to have a couple
of missing square brackets in the last expression - each fieldname should be
enclosed in brackets, and if it contains a blank it MUST be enclosed in
brackets. Try

Total Sales: ([Quantity] * [Taxable Items Price]) + ([Quantity] * [Nontaxable
Items Price])

Note that this assumes that the same items aren't found in both price fields,
otherwise they'll be double counted.

John W. Vinson [MVP]

 

[Guest]

Thank you for your help. I had actually tried probably a dozen different
ways of writing the expression, and still couldn't get it to work. After
trying your expression, it didn't work, and knowing it should, I went back
and looked at the data. Turns out expression won't add if there is a null in
the field. I had to go back and add 0's to all the null fields, and then it
worked.

 

[John W. Vinson]

Turns out expression won't add if there is a null in
the field. I had to go back and add 0's to all the null fields, and then it
worked.

The NZ() function will fix nulls in your tables - sorry, I should have thought
of that! In the future try

Total Sales: ([Quantity] * NZ([Taxable Items Price],0)) + ([Quantity] *
NZ([Nontaxable Items Price]))

(assuming that Quantity is non-NULL).

John W. Vinson [MVP]

 

[Guest]

Thank you! That was very helpful, and will make things run much better!