Value will NOT calculate

[Guest]

Hi:

I have a database that I am working on. The data is coming from an AS400
into Access 2003. I have "4" Queries built on 2 TABLES. Each QUERY builds on
the previous one.

I have several $$ fields in this QUERY related to payroll dollars

RegularTotal
OT Total
Other Total
Total Burden
Reimbursements

I want to add these "5" fields to get a total. I am going into my QUERY and
adding the following

Total: [Regular Total]+[OT Total]+[Other Total]+[Total
Burden]+[Reimbursements]

If I leave the "REIMBURSEMENTS" field OUT of the expression, I get the
following result...ie...the other 4 fields calcuate correctly.

However, as soon as I add the "REIMBURSEMENTS" field....the total does NOT
calculate anything BUT the REIMBURSEMENTS field. Every other field that has
dollar values in Regular Total, OT Total, Other Total, and Total Burden are
-0-.

I'm wondering if this may be related to a condition I have in an earlier
QUERY

Reimbursements: IIf([Adjustment Code]="NT",[Adjustment],Null)

To test this.....I took the results of my last QUERY...and ran an "APPEND"
Query to take the data.....and get into a stand-a-lone TABLE. I thought this
would flush out anything that might be causing this......but......When I run
a calculation on these "5" fields from this new TABLE....I still get the same
results.

I have also confirmed that ALL 5 fields in this new TABLE do indeed have a
DATA TYPE of NUMBER.

This is the strangest thing I have ever seen.....Any ideas???

 

[Guest]

Try changing to this --
Reimbursements: IIf([Adjustment Code]="NT",[Adjustment],0)

 

[S.Clark]

[Anything] + Null = Null

[Anything] + Nz(Null,0) = [Anything] + 0


--
Steve Clark, Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Smartin]

Hi:

I have a database that I am working on. The data is coming from an AS400
into Access 2003. I have "4" Queries built on 2 TABLES. Each QUERY builds on
the previous one.

I have several $$ fields in this QUERY related to payroll dollars

RegularTotal
OT Total
Other Total
Total Burden
Reimbursements

I want to add these "5" fields to get a total. I am going into my QUERY and
adding the following

Total: [Regular Total]+[OT Total]+[Other Total]+[Total
Burden]+[Reimbursements]

If I leave the "REIMBURSEMENTS" field OUT of the expression, I get the
following result...ie...the other 4 fields calcuate correctly.

However, as soon as I add the "REIMBURSEMENTS" field....the total does NOT
calculate anything BUT the REIMBURSEMENTS field. Every other field that has
dollar values in Regular Total, OT Total, Other Total, and Total Burden are
-0-.

I'm wondering if this may be related to a condition I have in an earlier
QUERY

Reimbursements: IIf([Adjustment Code]="NT",[Adjustment],Null)

That looks suspect. Inserting a Null into an addition problem will
produce a Null result regardless of the other values. Try:

Reimbursements: IIf([Adjustment Code]="NT",[Adjustment],0)

And do the same anywhere else you might be using forcing results to Null.

I don't think that explains everything though. I would expect

Total: [Regular Total]+[OT Total]+[Other Total]+[Total
Burden]+[Reimbursements]

to return either a correct sum (if all addends are numbers) or Null (if
any addend is Null). Not, as you say, /only/ Reimbursements.