Table defaults not used when adding record in code

I have a table with about 80 fields. I'm using an import process to
populate the table. It works fine, except for the following:
>
Users generally don't specify values for a lot of numerical fields on
the spreadsheet. I set the Access table up so that all the numerical
values would have a default value of zero.
>
I thought that when the record was appended to the table that the
numerical fields that do not have values would be defaulted to zero
by Access. This is not happening. The field values show nothing in
the table.
>
How do I get Access to default these blank values to zero in the
table using my import process.
>
Thanks.

rdemyan via AccessMonster.com wrote:>
A default is not applied when you append a row with a null in that field.
The default is applied when you append a row that does not include that
field at all.
>
For example: If I have a table with two fields [ID] and [SomeNumber] and
[SomeNumber] has a default value of zero then these are the results I will
get from two different append queries...
>
INSERT INTO TableName
VALUES(1, Null)
>
result: [ID]=1 SomeNumber = Null
>
>
INSERT INTO TableName
VALUES(1)
>
result: [ID] = 1 SomeNumber = 0
>
So as long as your Excel import includes all fields then your defaults
will never be applied.
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
>
>

you could then cycle the tabledef and run an update query for each
field a bit like this to make all the nulls zeros
This will of course mean that 80 queries will run
>
'Code assumes you are using DAO
>
Sub SubMakeNullZeros()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = CurrentDb
Set tdf = db.TableDefs("MyTableName")
For Each fld In tdf.Fields
db.Execute "UPDATE [" & tdf.Name & "] SET [" & fld.Name &
"] = 0
WHERE [" & fld.Name & "] IS NULL"
Next
Set tdf = Nothing
Set db = Nothing
>
msgbox "Done"
End Sub

"tombsy" <iclmailnospam@tiscali.co.ukwrote in
news:45327797$1_3@mk-nntp-2.news.uk.tiscali.com:
>>
A better what to do this would be to use a recordset that includes
only the numeric fields that you want to set to 0.
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns985DBE583F90Bf99a49ed1d0c49c5bbb2@127.0.0. 1...>
Since all the numeric fields were intended to default to zero,
couldn't you just test for datatype as you walk the fields and
apply the query only to numeric fields?