Null values + INSERT INTO problem

ChuiceBox

New member
Local time
Today, 00:44
Joined
Nov 6, 2011
Messages
3
Hi,

I'm have some VBA code which uses DAVG to calculate average of several columns in my table based on a column selected in a combo box, It then appends these averages to the same table using INSERT INTO. That all works UNTIL davg has a selection of null values and trys to pass a null to INSERT INTO which then spits the dummy.

So my question after that confusing paragraph is how can I deal with null values when using INSERT INTO. Also might be worth noting that the DAVG values are all assigned as variables before being inserted.
So my insert into looks something like:

Code:
CurrentDb.Execute "INSERT INTO Tester (StockPileID, Crushed_Ton, Fe_Per)VALUES ('" & StockPileCombo & "', " & StockPileDifference & ", " & FeAverage & ")"

Any help would be very much appreciated as I am stumped on this.
 
Welcome to the forum!

It's bad practice to save derived values only in exceptional cases. Calculated values should be re-calculated whenever you want to use the value again.

Also, instead of using DAvg, have you considered using Avg in a Totals query?
 
Hvae you tried using Nz() ?

+1 for what VbaInet said
 
Hvae you tried using Nz() ?
Nz() won't cut it. Null needs to be inserted and it won't work if it's enclosed in single quotes for example. stockpilecombo is the main culprit.
 

Users who are viewing this thread

Back
Top Bottom