|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have the following MS Access 2000 query:
INSERT INTO tbl_CLIENT ( CONSUMER_UUID, CLIENT_ID, CREATE_DATETIME,
CREATE_USER, LUPDATE_DATETIME, LUPDATE_USER, FIRST_NAME, LAST_NAME,
FULL_NAME, SSN, INFO_RELEASE_AUTHORIZED )
SELECT LCAClient_tblGroupedClients.Consumer_UUID,
LCAClient_tblGroupedClients.SamsID AS CLient_ID, IIf([InputDate] Is
Null,Format(Now(),"Short Date"),Format([InputDate],"Short Date")) AS
CREATE_DATETIME, {guid {AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS
CREATE_USER, IIf([LastUpdate] Is Null,Format(Now(),"Short
Date"),[LastUpdate]) AS LUPDATE_DATETIME, {guid
{AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS Lupdate_User,
LCAClient_tblGroupedClients.FirstName AS FIRST_NAME,
LCAClient_tblGroupedClients.LastName AS LAST_NAME, [LastName] & ", " &
[FirstName] AS FULL_NAME, IIf([SSN] Is Null,"000000000",[SSN]) AS SOC,
LCAClient_tblGroupedClients.INFO_RELEASE_AUTHORIZED
FROM LCAClient_tblGroupedClients
WHERE (((LCAClient_tblGroupedClients.TitleIII)=True));
The problem is with the INFO_RELEASE_AUTHORIZED field which is a required
Bit (yes/no) field in an SQL server table (tbl_CLIENT). The query worked OK
last week but today there was a problem with Access and the database rebuilt
itself (did a compact/repair). Ever since this I get an error when I try to
do the append: "You tried to assign the NULL value to a vairable that is not
a variant type.". The table that the query is drawing from has that field
set to a default of 0 so it is not null. I have also tried assigning the
value manually (InfoRelease: 0) but that does not work either. Any ideas on
how to recover from this?
|
|
Bill,
If you want the default value to be applied, then your insert statement
should not include that column:
> INSERT INTO tbl_CLIENT ( CONSUMER_UUID, CLIENT_ID, CREATE_DATETIME,
> CREATE_USER, LUPDATE_DATETIME, LUPDATE_USER, FIRST_NAME, LAST_NAME,
> FULL_NAME, SSN )
Or you could substitute 0 (zero) for Null:
> Nz(LCAClient_tblGroupedClients.INFO_RELEASE_AUTHORIZED, 0)
Good luck,
Mattias
From: Bill Cart, on 6/5/2006 7:37 PM:
[Quoted Text] > I have the following MS Access 2000 query:
>
> INSERT INTO tbl_CLIENT ( CONSUMER_UUID, CLIENT_ID, CREATE_DATETIME,
> CREATE_USER, LUPDATE_DATETIME, LUPDATE_USER, FIRST_NAME, LAST_NAME,
> FULL_NAME, SSN, INFO_RELEASE_AUTHORIZED )
> SELECT LCAClient_tblGroupedClients.Consumer_UUID,
> LCAClient_tblGroupedClients.SamsID AS CLient_ID, IIf([InputDate] Is
> Null,Format(Now(),"Short Date"),Format([InputDate],"Short Date")) AS
> CREATE_DATETIME, {guid {AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS
> CREATE_USER, IIf([LastUpdate] Is Null,Format(Now(),"Short
> Date"),[LastUpdate]) AS LUPDATE_DATETIME, {guid
> {AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS Lupdate_User,
> LCAClient_tblGroupedClients.FirstName AS FIRST_NAME,
> LCAClient_tblGroupedClients.LastName AS LAST_NAME, [LastName] & ", " &
> [FirstName] AS FULL_NAME, IIf([SSN] Is Null,"000000000",[SSN]) AS SOC,
> LCAClient_tblGroupedClients.INFO_RELEASE_AUTHORIZED
> FROM LCAClient_tblGroupedClients
> WHERE (((LCAClient_tblGroupedClients.TitleIII)=True));
>
>
> The problem is with the INFO_RELEASE_AUTHORIZED field which is a required
> Bit (yes/no) field in an SQL server table (tbl_CLIENT). The query worked OK
> last week but today there was a problem with Access and the database rebuilt
> itself (did a compact/repair). Ever since this I get an error when I try to
> do the append: "You tried to assign the NULL value to a vairable that is not
> a variant type.". The table that the query is drawing from has that field
> set to a default of 0 so it is not null. I have also tried assigning the
> value manually (InfoRelease: 0) but that does not work either. Any ideas on
> how to recover from this?
|
|
I have done both and still get the same error. I also tried exporting to
Access 97 & 2002 format. I also copied all of the tables, queries, forms,
etc to a blank database and still get the same results.
The oddest time is that, if I open the query results and the table I can
copy the indivdual values one at a time from a row into the table and the
append then works fine. But if I try to paste a row in I get the error. I
don't understand that, if the values are correct all what is the error?
"Mattias Jonsson" <news[ at ]ett-remove-idesystem.se> wrote in message
news:udo%23kXViGHA.4580[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Bill,
> If you want the default value to be applied, then your insert statement
> should not include that column:
> > INSERT INTO tbl_CLIENT ( CONSUMER_UUID, CLIENT_ID, CREATE_DATETIME,
> > CREATE_USER, LUPDATE_DATETIME, LUPDATE_USER, FIRST_NAME, LAST_NAME,
> > FULL_NAME, SSN )
>
> Or you could substitute 0 (zero) for Null:
> > Nz(LCAClient_tblGroupedClients.INFO_RELEASE_AUTHORIZED, 0)
>
> Good luck,
> Mattias
>
> From: Bill Cart, on 6/5/2006 7:37 PM:
>> I have the following MS Access 2000 query:
>>
>> INSERT INTO tbl_CLIENT ( CONSUMER_UUID, CLIENT_ID, CREATE_DATETIME,
>> CREATE_USER, LUPDATE_DATETIME, LUPDATE_USER, FIRST_NAME, LAST_NAME,
>> FULL_NAME, SSN, INFO_RELEASE_AUTHORIZED )
>> SELECT LCAClient_tblGroupedClients.Consumer_UUID,
>> LCAClient_tblGroupedClients.SamsID AS CLient_ID, IIf([InputDate] Is
>> Null,Format(Now(),"Short Date"),Format([InputDate],"Short Date")) AS
>> CREATE_DATETIME, {guid {AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS
>> CREATE_USER, IIf([LastUpdate] Is Null,Format(Now(),"Short
>> Date"),[LastUpdate]) AS LUPDATE_DATETIME, {guid
>> {AECE5BE8-BF9E-4726-81CD-5F716BFEE564}} AS Lupdate_User,
>> LCAClient_tblGroupedClients.FirstName AS FIRST_NAME,
>> LCAClient_tblGroupedClients.LastName AS LAST_NAME, [LastName] & ", " &
>> [FirstName] AS FULL_NAME, IIf([SSN] Is Null,"000000000",[SSN]) AS SOC,
>> LCAClient_tblGroupedClients.INFO_RELEASE_AUTHORIZED
>> FROM LCAClient_tblGroupedClients
>> WHERE (((LCAClient_tblGroupedClients.TitleIII)=True));
>>
>>
>> The problem is with the INFO_RELEASE_AUTHORIZED field which is a required
>> Bit (yes/no) field in an SQL server table (tbl_CLIENT). The query worked
>> OK last week but today there was a problem with Access and the database
>> rebuilt itself (did a compact/repair). Ever since this I get an error
>> when I try to do the append: "You tried to assign the NULL value to a
>> vairable that is not a variant type.". The table that the query is
>> drawing from has that field set to a default of 0 so it is not null. I
>> have also tried assigning the value manually (InfoRelease: 0) but that
>> does not work either. Any ideas on how to recover from this?
|
|
Bill,
I don't see anything else wrong with it. Try to simplify the query -
remove nearly all columns until the error goes away, and then add them
back one at a time until you can identify the column causing the error.
Good luck,
Mattias
From: Bill Cart, on 6/6/2006 8:05 AM:
[Quoted Text] > I have done both and still get the same error. I also tried exporting to
> Access 97 & 2002 format. I also copied all of the tables, queries, forms,
> etc to a blank database and still get the same results.
>
> The oddest time is that, if I open the query results and the table I can
> copy the indivdual values one at a time from a row into the table and the
> append then works fine. But if I try to paste a row in I get the error. I
> don't understand that, if the values are correct all what is the error?
|
|
|