Run-time error '94'

[Z1nkstar]

Invalid use of Null



I don't believe I even used null in this sequence?

[ItsMe]

NewPos is an integer. Integers can not accept Null

Apparently, the field AllSort does not have a value. You can not do math on Null. Null + 1 = Null

You will need to do data validation of the field/control before assigning it to an integer variable.

[Z1nkstar]

So how can I set the value?
All I need is it to ascend starting at 1 in the sort column???

[ItsMe]

I do not know your data and your objective but, you can set a default value of 0 to your control and or field. This will likely avoid the error, at least in most cases.

[Z1nkstar]

well it is a number field and it doesnt have any set value but i put in default to 0 but that won't fill in the records that are currently there

[ItsMe]

Maybe validate the field in your code.
If isnull(Me!AllSort.Value) then
Me!AllSort.Value = 0
End if

You could run an UPDATE query to change the Null fields to equal zero.

Make a copy to test new and radical action queries like UPDATE queries. Make sure you do not destroy your table.

The query builder could guide you. Your UPDATE query could include criteria to only retrieve Is Null for that field. Then, in the Update To field you would place 0.

The fact that it is a number field makes things a little easier. You just need to make sure that the default value of zero is never erased. For instance, if there is a control on a form that is bound to AllSort, a user may be able to delete the value, save the record, and levae the field as Null.

Giving it a default value is not enough alone. You need to maintain constraints so there is not the possibility it will become Null, at least not be Null just before your code runs.