Solved

Access 97: Calculated Field Problem

Posted on 2003-12-05
8
335 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

777 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