Append Null to date/time field

Taras
Taras used Ask the Experts™
on
I am appending records from one table to another
And I am getting error that records are not appended because of validation rule error.


In table tblMasterRep Date filed is date/time filed type
In table for this field there is no option for allow Null value or not.

In table tblArchivedMaster Date filed is date/time type and could have null value.


Here is my query:

INSERT INTO tblMasterRep ( [DATE] )
SELECT IIf(IsNull([DATE]),Null,[DATE])
FROM tblArchivedMaster;

I tried with : IIf(IsNull([DATE]),”Null”,[DATE])
 Not working

Any Idea?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Instead of using Null or ”Null”, try an actual valid date like "01/01/0001". Use some date that cannot be confused with a 'real' date.

If a null value is valid in the tblArchivedMaster table, the real answer should be that tblMasterRep should also allow null values. But if that cannot be changed, you'll need to use a value that will pass validation and that you can still recognize as a substitute for a null value.

Tom
Most Valuable Expert 2012
Top Expert 2013

Commented:
Are you dealing with an Access table?  You should be able to directly insert nulls...

Check the validation rules and required properties in your table design.
FieldProperties.png
Most Valuable Expert 2012
Top Expert 2013
Commented:
Your query should work like this (in Access) if your field properties are set up correctly:

INSERT INTO tblMasterRep ( [DATE] )
SELECT [DATE]
FROM tblArchivedMaster;

Also, this is assuming that your table has an autonumber field, so that at least one field has a value when you append records with null dates.  Without an autonumber, you would be inserting a row with no values in any field - which as far as I know is not possible/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial