Table Design - Creating Empty Date Field

[Guest]

I have a make table query that creates a null field called ClosedDate. This
is essentially a place holder for an append query that later places data into
this field.

However, when the data gets appended, the field type for this field is
BINARY. How can I make that field be formatted as date/time?

 

[Allen Browne]

Use IIf(), e.g.:

SELECT IIf(False,#1/1/2000#,Null) AS MyDateField INTO MyTable;

False is never true, so the Jan 1 date never gets inserted, but it's enough
to give JET a clue about the intended data type.

 

[Guest]

This worked great - I really wanted to use in on the field creation though,
so I modified my original MakeTable query so that the DateClosed field read
like this in the QBE grid:

DateClosed: IIf(False,#01/01/2000#,Null)

Worked like a champ! Many thanks!