Solved

Access 97: Calculated Field Problem

Posted on 2003-12-05
8
333 Views
Last Modified: 2010-05-18
I have created a simple calculated field in my query that merely adds three fields, all of which are in the same query and/or table.  Two of the fields are also calculated fields and the third is a field from the table.  I have named the field as TOTAL.  The problem is that whenever a returned record has a value in the 3rd field, the TOTAL does not calculate--the TOTAL field is blank.  However, when there is a value in one of the other 2 fields, and not in the 3rd field, the TOTAL field  does calculate properly.  

This is such a simple calculated field, but I cannot get it to work.  I have checked the field properties and can find nothing wrong and no error messages are given.  What am I probably doing incorrectly?
0
Comment
Question by:borleymsgs
  • 3
  • 3
  • 2
8 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9885841
Hey borleymsgs!

  You need to include the calculations from both of the other calc fields and include that as part of the formula in the third.

  The values of the dependent calc is not being finished before the third field tries to calculate.

regards
Jack
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9885847
Calculated expressions should not be based on other calc expressions, for the very reasons you have indicated.
All calculations should be based on fixed data elements.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9886754
Hi borleymsgs

Can you post your sql script for us?

To view sql script:
Open the query in design view Menu > View > SQL

Fair comment Jack!
How ya doin?

Alan :)
0
 

Author Comment

by:borleymsgs
ID: 9888708
OK--I forgot about not being able to include previously calculated fields in a calculated field.  Upon analyzing my query, I decided that I did not even need to do that, so I simplified it.  But, the same problem still exists.  I am now merely adding two fields, but my TOTAL field only gives me the [sisAmount] value.  If a value exists in the [sisMF$Rcvd] field, TOTAL is blank.  I even used the Expression Builder to make sure I did not make any spelling errors.

Here is my SQL for the query:  Basiclly it is checking the tblSrvcInfrmtnSht table for all records that have the sisBill checkbox selected and selecting all records that fall within 6 seeks of the inputted parameter [Star Date]. For report purposes, it manipulates the Name Fields.  Also, for report purposes, I classify the [sisAmount] field based upon the [mtMoveCode] field (it was those calculated fields that I previously added in TOTAL, but eliminated upon this revision).  But, again, TOTAL is only the sum of [sisAmount] and [sisMF$Rcvd] fields.

SELECT tblSrvcInfrmtnSht.sisMvDt, tblSrvcInfrmtnSht.sisSqncNum, tblSrvcInfrmtnSht.mtMoveCode, tblSrvcInfrmtnSht.sisCityCrnt, tblSrvcInfrmtnSht.sisStateCrnt, tblSrvcInfrmtnSht.sisCityDest, tblSrvcInfrmtnSht.sisStateDest, tblSrvcInfrmtnSht.sisAmount, IIf([mtMoveCode]="mach",[sisAmount],0) AS [Mach$], IIf([mtMoveCode]<>"MACH",[sisAmount],0) AS [NonMach$], tblSrvcInfrmtnSht.[sisMF$Rcvd], IIf(IsNull([tblSrvcInfrmtnSht]![sisNameLast]),[tblSrvcInfrmtnSht]![sisNameFirst],[tblSrvcInfrmtnSht]![sisNameLast]) AS NAME, tblSrvcInfrmtnSht.sisBill, [sisAmount]+[sisMF$Rcvd] AS TOTAL
FROM tblSrvcInfrmtnSht
WHERE (((tblSrvcInfrmtnSht.sisMvDt) Between [Start Date:] And (DateAdd("ww",6,[Start Date:]))) AND ((tblSrvcInfrmtnSht.sisBill)=-1))
ORDER BY tblSrvcInfrmtnSht.sisMvDt, tblSrvcInfrmtnSht.sisSqncNum;

Here is a portion of the resulting Query DataView:

Amount$      Mach$      Borley$      MF $ Rcvd      Billed      TOTAL
$416.00      $0.00      $416.00      $0.00      Yes      $416.00
      $0.00            $4,542.97      Yes      
$710.00      $710.00      $0.00      $0.00      Yes      $710.00
$456.50      $0.00      $456.50      $0.00      Yes      $456.50
$312.00      $0.00      $312.00      $0.00      Yes      $312.00
$166.40      $0.00      $166.40      $0.00      Yes      $166.40


I hope you can find where I am going astray!  Thanks!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:jadedata
ID: 9888867
hey alan, I'm doing just fine, Oh Wizard of MS Access!!
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 125 total points
ID: 9889811
Hi borleymsgs

This might do it for you.

The columns that manipulates mtMoveCode may need to be combined using a nested IIF, but try it without first.
IIf([mtMoveCode]="mach",[sisAmount],IIf([mtMoveCode]<>"MACH",[sisAmount],0)0) AS [Mach$],


SELECT
  sisMvDt,
  sisSqncNum,
  mtMoveCode,
  sisCityCrnt,
  sisStateCrnt,
  sisCityDest,
  sisStateDest,
  sisAmount,
  IIf([mtMoveCode]="mach",[sisAmount],0) AS [Mach$],
  IIf([mtMoveCode]<>"MACH",[sisAmount],0) AS [NonMach$],
  [sisMF$Rcvd],
  IIf(IsNull([sisNameLast]),[sisNameFirst],[sisNameLast]) AS NAME,
  sisBill,
  IIf(IsNull([sisAmount],0)+IIf(IsNull([sisMF$Rcvd],0) AS TOTAL

FROM tblSrvcInfrmtnSht

WHERE (((sisMvDt) Between [Start Date:]
  And (DateAdd("ww",6,[Start Date:])))
  AND ((tblSrvcInfrmtnSht.sisBill)=-1))
ORDER BY sisMvDt, sisSqncNum;


Alan :)
0
 

Author Comment

by:borleymsgs
ID: 9900111
You found the problem--I forgot about having to convert null values to zeroes. I had to change the syntax of your IIf statements to include the false part, but at least you got me on the right track.  The simplest things can lead a novice astray!  Thanks!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9900137
Good one borleymsgs!

Yeah, them nested iif's take  bit of fiddling to get right.

Alan :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now