Null to 0

[Jerseynjphillypa]

I am trying to get my field name Saved Null to 0 but it is not working. Since the field is left blank where there is no saving

I tried the following


Nz([Saved],0)
Iff([Saved] is Null,0,[Saved])

I am not sure if I am typing in the function correctly since I just recently started using Access. Or is it because I am using the Sum Total Function.

Sorry but I just recently started using Access and learning as I go.

[June7]

Only problem I had with the query was a circular reference on the Saved field. The following worked:
SELECT Amount.Item, Amount.Date, Supply.Supply, Supply.Purchaser, Supply.Location, Sum(Amount.Cost) AS SumOfCost, Sum(Amount.Spend) AS SumOfSpend, Sum(Nz([Saved],0)) AS SavedAmt INTO [Total Purchase]
FROM Supply LEFT JOIN Amount ON Supply.Supply = Amount.Item
GROUP BY Amount.Item, Amount.Date, Supply.Supply, Supply.Purchaser, Supply.Location;

If you use SELECT INTO the Total Purchase table will be deleted first. Is this what you want to happen every time? To actually append records use INSERT SELECT.

[Jerseynjphillypa]

Only problem I had with the query was a circular reference on the Saved field. The following worked:
SELECT Amount.Item, Amount.Date, Supply.Supply, Supply.Purchaser, Supply.Location, Sum(Amount.Cost) AS SumOfCost, Sum(Amount.Spend) AS SumOfSpend, Sum(Nz([Saved],0)) AS SavedAmt INTO [Total Purchase]
FROM Supply LEFT JOIN Amount ON Supply.Supply = Amount.Item
GROUP BY Amount.Item, Amount.Date, Supply.Supply, Supply.Purchaser, Supply.Location;

If you use SELECT INTO the Total Purchase table will be deleted first. Is this what you want to happen every time? To actually append records use INSERT SELECT.

I would want the table to append records, so where would I use the command INSERT SELECT.

Also would I need to first create the table using SELECT INTO and then change it to INSERT SELECT so the records will append to the table.

[June7]

Yes, you could use the SELECT INTO one time or manually build table. Then just use the INSERT SELECT thereafter. Use the command INSERT SELECT in sql statement.

http://msdn.microsoft.com/en-US/libr...=SQL.105).aspx