Solved

Access 97: Calculated Field Problem

Posted on 2003-12-05
8
338 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
[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
  • 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
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!

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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