Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Changing the values in a calculated field

Posted on 2011-02-22
1
Medium Priority
?
356 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--
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: 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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

636 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