Member Avatar for shar164

I can't figure out how to perform a calcuation in a query and have the result output for use in a chart within a report. Forgive me I'm a newbie

  • 5 Contributors
  • 8 Replies
  • 255 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by Member #516515

Dani AI

Generated

Short practical answer and checklist for Access queries and charts:

When you want a calculated column in a query, type the expression into the Field row in Design view and leave the Table column blank. Do not put numeric constants inside square brackets — brackets tell Access to treat an item as a field name. If the Table column is filled or a constant is bracketed, Access may try to qualify the literal with the table and return a syntax error (this is what caused the issue reported by ). was correct about placing expressions in Field, and ’s tip to leave Table empty is the crucial follow-up.

Typical safe patterns (examples):

TaxAmount: Nz([UnitPrice],0) * 0.075

Use Nz(...) to avoid NULLs. If a numeric field is stored as text, convert it before math (Val, CDbl or CCur for currency). For rounding/formatting keep calculations numeric and only format for display in the report:

Premium: CCur(Round(Nz([HomeValue],0) * 0.0012, 2))

Chart/report tips: decide whether the chart needs raw rows or aggregated values. If you need totals/averages, either add a Totals row in the query (GROUP BY / SUM) or create a separate summary query and use that as the chart’s RowSource. Make sure the field you bind to the chart is a numeric type (unformatted), since Format functions return text and break numeric charting. ’s point about whether you need grouping is important when building chart data.

Troubleshooting checklist: clear the Table column for calculated fields; remove brackets around numeric literals; wrap fields with Nz() to avoid Nulls; convert text-to-number when needed; save the query before pointing a report/chart to it. These steps resolve the common syntax error and make the calculated value usable in charts.

Recommended Answers

All 8 Replies

Member Avatar for cpopham

Okay, first things first. What type of calculation are you attempting to do in the query? Are you calculating across multiple fields? Please provide a little more information about the calculation and the table structure if any the calculated fields are based upon.

Chester

Member Avatar for jbennet

i just did this for my coursework

in the Field: section under the queries design view you can tyupe things like this.

GPPerCentre: [num_candidates]*[payment_eachscript]

(where gppercentre is what the field will be called, and num_candidates and payment_eachscript are existing firlds in the query)

Member Avatar for cpopham

You can also use queries to calculate fields from several tables, perform regular calculations, etc. You can also perform calculations inside a report based on grouping and that may be easier, but I need to know a little more about what the calculation is and the underlying table structure.

Member Avatar for Member #516515

Hey this helps me too - thanks.

Except a little difference for me: I'm wanting a calculation using a decimal constant as follows:
HomePremium: [HomeSum]*[0.00119371]

What I'm trying to do is auto calculate a house insurance premium based on the user input of a Sum Insured called HouseSum.

Eg: Enter $300,000 for HouseSum and the autocalculation in the query multiplies this by a constant (which I have chosen/invented for simplicity) giving the answer: $358.13

But MS Access wont accept my constant. It will multiply two fields easy enough, but not [field] * my [constant].

Where am I going wrong?
Cheers

Member Avatar for HI2Japan

Is your field called HomeSum or HouseSum? You have it written both ways in your question. Try it without putting [ or ] around your constant.

Member Avatar for Member #516515

Is your field called HomeSum or HouseSum? You have it written both ways in your question. Try it without putting [ or ] around your constant.

Thanks for your reply. Yes it should be HomeSum not HouseSum. But that wasnt my problem. I tried it without [] around my constant, but I still got a "syntax error in query expression 'tblHomeProp.0.011937*[HomeSum]' "

I have chosen (in the "table" field located under the expression) the table that contains the field "HomeSum". I assume I need this. This causes the syntax error. When I leave the "table" field blank then there is no syntax error. Whats the story?

Member Avatar for HI2Japan

Check out this little sample I just made. Is this what you are trying to do?

Also, if you are creating the expression, then the table field should be left blank. The query will not add this data to your table nor is the new field you are creating in your table. It is basically just a display only look at it so you do not need to put in anything in the table field.

Member Avatar for Member #516515

Thanks so much for your help - spot on!
I have shifted house this week so sorry for delay in replying... See ya!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.