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
..
Eugene said:
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.]