Solved

Changing the values in a calculated field

Posted on 2011-02-22
1
317 Views
Last Modified: 2012-05-11
Hello,

I have a MS Access DB that uses several queries.  Some of these queries have calculated fields of a currency value.  One of these calculated fields is called 'Balance Owing' - I want to take all the entries for 2009, and give them a $0.00 balance, as they're all paid off now.
But, when I tried doing that (by using an update query and using the BETWEEN command to search the date range), I got a message that said you cannot change the values of calculated fields.
0
Comment
Question by:--TripWire--
1 Comment
 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 34951727
You can only update tables.  It sounds like you are trying to update a query.
If you want a zero result in your query you either have to set the values in the table so that a zero result will be obtained , or else you have to build into the query expression a test for the year and produce a result of zero..

Balance:iif (year(mydatefield)=2009, 0, calculationHere)
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question