Access table DefaultValue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table, Table3, set up with a default value of 0 in its [Value] (data
type =longint) field. The [Key] field in all tables is the primary key. I
execute the following query:

INSERT INTO Table3 ( [Key], [Value] )
SELECT Table1.Key, Table2.Value
FROM Table1 LEFT JOIN Table2 ON Table1.Key = Table2.Key;

Where there is no matching record in Table2, I expect Table3.Value=0
(default), but instead I get Null? Am I missing the point of a field default?
 
The default value will be used when a record is inserted into the table, but
no value is inserted into the field with the default value. But your insert
query does insert a value into the field - the Null value. To insert a 0
into the Value field when the query returns a Null value, you could use the
NZ function or the IIf function. (Either function will work in Access, but
the NZ function is Access-specific and will not work if you ever need to use
this query outside of the Access environment.

INSERT INTO Table3 ([Key], [Value])
SELECT Table1.Key, IIf(Table2.Value IS NULL, 0, Table2.Value)
FROM Table1 LEFT JOIN Table2 ON Table1.Key = Table2.Key;
 
Thanks for your help
--
Fred


Brendan Reynolds said:
The default value will be used when a record is inserted into the table, but
no value is inserted into the field with the default value. But your insert
query does insert a value into the field - the Null value. To insert a 0
into the Value field when the query returns a Null value, you could use the
NZ function or the IIf function. (Either function will work in Access, but
the NZ function is Access-specific and will not work if you ever need to use
this query outside of the Access environment.

INSERT INTO Table3 ([Key], [Value])
SELECT Table1.Key, IIf(Table2.Value IS NULL, 0, Table2.Value)
FROM Table1 LEFT JOIN Table2 ON Table1.Key = Table2.Key;

--
Brendan Reynolds (MVP)

Fred said:
I have a table, Table3, set up with a default value of 0 in its [Value]
(data
type =longint) field. The [Key] field in all tables is the primary key.
I
execute the following query:

INSERT INTO Table3 ( [Key], [Value] )
SELECT Table1.Key, Table2.Value
FROM Table1 LEFT JOIN Table2 ON Table1.Key = Table2.Key;

Where there is no matching record in Table2, I expect Table3.Value=0
(default), but instead I get Null? Am I missing the point of a field
default?
Click to expand...
Click to expand...
 
Back
Top