Problem with adding 2 field contents together in a query.

[brianb]

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.

 

[Fornatian]

Try using the sum functiion:

Expr1:Sum([Field1],[Field2])

If that doesn't work look at your field properties and make sure they are numeric, if they are not numeric you may have to convert them to Integers or Singles in the expression. It is more prudent however to make the correction to the field property as this problem can only crop up again left untethered.

 

[brianb]

Thanks Fornation & Pat Hartman.

As PH said, the Sum() one did not work. His NZ() answer did though. Very grateful.

This also works :- Total: IIf([Expenditure PTD]=0,[JUL],[JUL]+[Expenditure PTD])

I am looking forward to getting this into Excel where I am on more firm ground <g>.


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

 

[Fornatian]

My apologies Brian - cross border posting - not a good thing

 

[brianb]

Fornation. No problem. Just keep going.