Table defaults not used when adding record in code

rdemyan via AccessMonster.com

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 15 '06 #1

Rick Brandt

rdemyan via AccessMonster.com wrote:

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.

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

Oct 15 '06 #2

tombsy

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
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:JC******************@newssvr13.news.prodigy.c om...

rdemyan via AccessMonster.com wrote:

>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.

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

Oct 15 '06 #3

David W. Fenton

"tombsy" <ic***********@tiscali.co.ukwrote in
news:45**********@mk-nntp-2.news.uk.tiscali.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

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/

Oct 15 '06 #4

Kc-Mass

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?

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"tombsy" <ic***********@tiscali.co.ukwrote in
news:45**********@mk-nntp-2.news.uk.tiscali.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

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/

Oct 16 '06 #5

David W. Fenton

"Kc-Mass" <co********@comcast.netwrote in
news:1f******************************@comcast.com:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"tombsy" <ic***********@tiscali.co.ukwrote in
news:45**********@mk-nntp-2.news.uk.tiscali.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

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.

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?

Well, sure, but that's only better if you're creating a generic
subroutine. If you're hardwiring the tablename (as the code does),
then it's going to be more efficient to define which fields you want
it applied to.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 16 '06 #6