Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 97: Calculated Field Problem

Posted on 2003-12-05
8
Medium Priority
?
343 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

824 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