MS Access Query problem total 2 fields

[BrainB]

Hi All

I am having great problems with a Query in Microsoft Access 97 which combines 2 tables where I am trying to create a calculated field to add the contents of 2 fields together (one from each table). I keep getting a blank field in data view. I have checked that the fields are Numeric format. I have tried various ways of defining the field, and various forms of parenthesis. Using Query Design view.
'------------------------------------

I first tried :-
Expr1: [JUL]+[Expenditure PTD] - also [Table1]![JUL]+[Additions]![Expenditure PTD]

If I put Expr1: [JUL] * 1 this works.
If I put Expr1: [Expenditure PTD] * 1 this works.

'------------------------------------
Next I made 3 new calculated fields :-
July:[JUL]*1 works correctly.
Add:[Expenditure PTD]*1 works correctly.

but I still cannot add them to get :-
Total:[July] + [Add] - only a blank field.

[July]*1 and [Add]*1 both work correctly.
'---------------------------------------

A wierd situation. I can calculate a Year to Date figure in Table1 with [JAN]+[FEB]+[MAR] ... etc. with no problem. Found nothing searching the Web. And, as usual, this is something critical in what I am attempting to do. Perhaps it's back to Excel.

Regards
BrianB
========================================

 

[Steve101]

Brian, can you publish the SQL from the query; this might help us to understand whats happening.

Also try the following:

instead of        [July] + [add] 
try               nz([July]) + nz([add])

let us know if that makes a difference; otherwise lets see the query,

Cheers
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BrainB]

Hi Steve

Thanks for your help. That solved my problem. I also found that this works too:
Total: IIf([Expenditure PTD]=0,[JUL],[JUL]+[Expenditure PTD])
Total2: [JUL]+NZ([Expenditure PTD])

Regards
BrianB
============================================ Regards
BrianB
================================