Insert query with null column and null value

[Guest]

Hi, I have a Date/Time column that is not a mandatory field. But when I do
INSERT INTO (...) SELECT ..., the query fails with "You tried to assign the
Null value to a variable that is not a Variant data type".

If I remove the column altogether from both the INSERT and SELECT, then the
insertion query will be successful.

How can I fix it? Thanks.

 

[Guest]

I'm not sure if you're doing this in SQL or VBA, so I'll give a generic
answer. Tailer to your needs.

Options: Choose one some or all...
1. Set a default value in the table. (e.g. 01/19/1919)
2. Check if the value is null, if so, set it to the default value. (IF or
IIF or Nz)
3. Check if the value is null, is so, alter the SQL to exclude the field. (IF)
4. Try implementing function cdate(). May need to use Nz() with it.

 

[Guest]

Hi Steve, thanks.

I won't be able to put in a default value to it. So I probably would have to
live with the third or fourth options.

How shall I use the third option? I don't really understand what you mean by
alter the SQL when it is null? I would have to select and insert multiple
records, and among them, I am sure that some of the rows will have value and
some will not have value for the said column.

Actually, what is the reason for this behaviour when the column is actually
not mandatory?

Steve, I am using SQL in MS Access query to select/insert, but it is invoked
thru vba (form).

Thanks

Eugene

 

[John Spencer]

Smart thing is to POST your code so we can see what you are doing.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.FinishTime
FROM tmpPacking;

FinishTime is the column having the problem.

Thanks.

 

[John Spencer]

If that is your actual query then you have a table name that is not in the
FROM Clause of the SELECT query tPacking is not the same as tmpPacking.

Beyond that I see no reason for a problem unless tPack.FinishTime will not
allow nulls. I'm not sure how you would get an error message that would
tell you that FinishTime is the problem with the code you posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

yes John, I have modified the sql slightly before I post it due to the nature
of the table name and number of columns.

As you mentioned, I am myself is puzzled over why the FinishTime cause the
problem. I have other time column in the table, but I didn't include them in
the query, and when I don't even include the FinishTime column, then the
query runs fine.

 

[John Spencer]

Have you tried (as a test) to just assign a value? Assuming that Finish
time is a DateTime field, does something like the following work?


INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, #00:00:01#
FROM tPacking;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, #00:00:01#
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId )
SELECT tPacking.MachineId, tPacking.OperatorId
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId, StartTime, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.StartTime,
tPacking.StartTime
FROM tPacking;

# Yes, works. All row's StartTime contains a value.

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, Nz(tPacking.FinishTime,
#00:00:00#)
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.FinishTime
FROM tPacking;

# Fails. [You tried to assign the Null value to a variable that is not a
Variant data type.]

 

[John Spencer]

IS tPack.FinishTime involved in any relationships?
Does tPack.FinishTime have any restraint rule that it cannot be null? And a
default value that is not null?
IF you open tPack directly and attempt to enter a record and leave
tPack.FinishTime blank, do you get any error message?

The workaround (which should not be needed) would be to use two queries.
INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.FinishTime
FROM tPacking
WHERE tPacking.FinishTime is Not Null

and

INSERT INTO tPack ( MachineId, OperatorId)
SELECT tPacking.MachineId, tPacking.OperatorId
FROM tPacking
WHERE tPacking.FinishTime is Null


I would assume from all the testing you have done that the following would
fail also
INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, Null
FROM tPacking;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

tPack.FinishTime is not involved in a relationship, but it is a linked table.
I make the change from another MS Access database.

The field has no restraint rule on it not to be null.
No default value defined for the field.

When I open the table directly and enter a record with the field blank, it
doesn't prompt any error message.

Yes, to use the word Null fails as well.

Yes, the only workaround that I can think of is to use two queries, which
means two query objects, as I can join both the queries in the single query
object.

Thanks alot.

IS tPack.FinishTime involved in any relationships?
Does tPack.FinishTime have any restraint rule that it cannot be null? And a
default value that is not null?
IF you open tPack directly and attempt to enter a record and leave
tPack.FinishTime blank, do you get any error message?

The workaround (which should not be needed) would be to use two queries.
INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.FinishTime
FROM tPacking
WHERE tPacking.FinishTime is Not Null

and

INSERT INTO tPack ( MachineId, OperatorId)
SELECT tPacking.MachineId, tPacking.OperatorId
FROM tPacking
WHERE tPacking.FinishTime is Null


I would assume from all the testing you have done that the following would
fail also
INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, Null
FROM tPacking;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, #00:00:01#
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId )
SELECT tPacking.MachineId, tPacking.OperatorId
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId, StartTime, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.StartTime,
tPacking.StartTime
FROM tPacking;

# Yes, works. All row's StartTime contains a value.

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, Nz(tPacking.FinishTime,
#00:00:00#)
FROM tPacking;

# Yes, works.

INSERT INTO tPack ( MachineId, OperatorId, FinishTime )
SELECT tPacking.MachineId, tPacking.OperatorId, tPacking.FinishTime
FROM tPacking;

# Fails. [You tried to assign the Null value to a variable that is not a
Variant data type.]