Appending a blank (null) date to a SQL Server 2000 smalldatetime Field

  • Thread starter Thread starter R Bolling
  • Start date Start date
R

R Bolling

Hi -- I have an append query where I am appending to a SQL Server
table. One of the fields is WantedByDate, and periodically it is left
blank. Whenever I attempt an append, I get the error: "You tried to
assign the null value to a variable that is not a variant data type."

Initially, I set the field so that it would accept nulls -- that
didn't work.

So I tried creating the calculated field:
IIF(isnull[WantedByDate],date(),[WantedByDate]) which works great --
but the date() stamp is incorrect. I tried using nz([WantedByDate])
and that gives me a type mismatch. I can't change the data type on
SQL Server because it's a production table.

If the [WantedByDate] is null, it needs to be null in the SQL table.
Does anyone have any suggestions.

Thanks,

RBollinger
 
Why are you attempting to use any expression other than the field? Does the
sql server table allow null values in the field? Could you share your SQL
view of the append query?
 
Duane Hookom said:
Why are you attempting to use any expression other than the field? Does the
sql server table allow null values in the field? Could you share your SQL
view of the append query?

--
Duane Hookom
MS Access MVP


R Bolling said:
Hi -- I have an append query where I am appending to a SQL Server
table. One of the fields is WantedByDate, and periodically it is left
blank. Whenever I attempt an append, I get the error: "You tried to
assign the null value to a variable that is not a variant data type."

Initially, I set the field so that it would accept nulls -- that
didn't work.

So I tried creating the calculated field:
IIF(isnull[WantedByDate],date(),[WantedByDate]) which works great --
but the date() stamp is incorrect. I tried using nz([WantedByDate])
and that gives me a type mismatch. I can't change the data type on
SQL Server because it's a production table.

If the [WantedByDate] is null, it needs to be null in the SQL table.
Does anyone have any suggestions.

Thanks,

RBollinger
Click to expand...
Click to expand...


Hi Duane,

the SQL is as follows:

INSERT INTO dbo_DWUSOS ( NewUpdate, AccountNo, WantedByDate )
SELECT xxx.NewUpdate, xxx.AccountNo, xxx.WantedByDate
FROM xxx;

The problem field in SQL 2000 is WantedByDate (smalldatetime, allow
nulls). Problem only happens when the date is null. I get: "You
tried to assign the null value to a variable that is not a variant
data type."

Thanks,

RBollinger
 
The only thing that I can think of that might cause this is possibly a
trigger or other dependency in the table that is causing trouble.

--
Duane Hookom
MS Access MVP


R Bolling said:
"Duane Hookom" <[email protected]> wrote in message
Why are you attempting to use any expression other than the field? Does the
sql server table allow null values in the field? Could you share your SQL
view of the append query?

--
Duane Hookom
MS Access MVP


R Bolling said:
Hi -- I have an append query where I am appending to a SQL Server
table. One of the fields is WantedByDate, and periodically it is left
blank. Whenever I attempt an append, I get the error: "You tried to
assign the null value to a variable that is not a variant data type."

Initially, I set the field so that it would accept nulls -- that
didn't work.

So I tried creating the calculated field:
IIF(isnull[WantedByDate],date(),[WantedByDate]) which works great --
but the date() stamp is incorrect. I tried using nz([WantedByDate])
and that gives me a type mismatch. I can't change the data type on
SQL Server because it's a production table.

If the [WantedByDate] is null, it needs to be null in the SQL table.
Does anyone have any suggestions.

Thanks,

RBollinger
Click to expand...
Click to expand...


Hi Duane,

the SQL is as follows:

INSERT INTO dbo_DWUSOS ( NewUpdate, AccountNo, WantedByDate )
SELECT xxx.NewUpdate, xxx.AccountNo, xxx.WantedByDate
FROM xxx;

The problem field in SQL 2000 is WantedByDate (smalldatetime, allow
nulls). Problem only happens when the date is null. I get: "You
tried to assign the null value to a variable that is not a variant
data type."

Thanks,

RBollinger
Click to expand...
 
If the statement: IIF(isnull[WantedByDate],date(),[WantedByDate])
works fine. Is there anything that I can replace the date() with so
that it appears blank --or even something like "00/00/00"
 
SmallDate type won't accept 00/00/00. The earliest date would be around 1900
I think. I wouldn't put anything other than null in the table. I find it
hard to understand why the following doesn't work:

INSERT INTO dbo_DWUSOS ( NewUpdate, AccountNo, WantedByDate )
SELECT xxx.NewUpdate, xxx.AccountNo, xxx.WantedByDate
FROM xxx;

You could try to statements:
INSERT INTO dbo_DWUSOS ( NewUpdate, AccountNo, WantedByDate )
SELECT xxx.NewUpdate, xxx.AccountNo, xxx.WantedByDate
FROM xxx WHERE WantedByDate is not Null;

INSERT INTO dbo_DWUSOS ( NewUpdate, AccountNo )
SELECT xxx.NewUpdate, xxx.AccountNo
FROM xxx WHERE WantedByDate is Null;
 
Back
Top