?
Solved

Changing the values in a calculated field

Posted on 2011-02-22
1
Medium Priority
?
358 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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

571 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