Access 97: Calculated Field Problem

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?
borleymsgsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
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
jadedataMS Access Systems CreatorCommented:
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
Alan WarrenApplications DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

borleymsgsAuthor Commented:
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
jadedataMS Access Systems CreatorCommented:
hey alan, I'm doing just fine, Oh Wizard of MS Access!!
0
Alan WarrenApplications DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
borleymsgsAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
Good one borleymsgs!

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

Alan :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.