Format Field in Make Table Query

I know field formats are not inherited in a Make Table Query. Here is the
query.
SELECT DISTINCT tblDealer.DealerFirstName, tblDealer.DealerLastName,
tblDealer.Address, tblDealer.HomeTown, tblDealer.HomeState,
tblDealer.HomeZip, Date() AS [Date Mailed], Date() AS [Contract Received]
INTO tblCurrentContractList
FROM (tblDealer INNER JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID)
INNER JOIN tblShow ON tblEvent.ShowID = tblShow.ShowID
WHERE (((tblShow.EndShowDate)[In Shows after date input]))
ORDER BY tblDealer.DealerLastName;

At present I have the Contract Received field set to Date() to type it as a
date.
I want it to be typed as date/time but null so the client can type in the
date later. Is there some elegant way to do this?

In general, the best approach is to set up the table exactly as you want it,
and then populate it with an Append query instead of the Make Table.

If that doesn't suit, you can try this:
IIf(False, #1/1/2000#, Null)
The False part is never true, so it will never insert the date, but in some
cases that's enough to give JET the clue as to what data type you intend.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Claudette Hennessy" wrote in message
...
I know field formats are not inherited in a Make Table Query. Here is the
query.
SELECT DISTINCT tblDealer.DealerFirstName, tblDealer.DealerLastName,
tblDealer.Address, tblDealer.HomeTown, tblDealer.HomeState,
tblDealer.HomeZip, Date() AS [Date Mailed], Date() AS [Contract Received]
INTO tblCurrentContractList
FROM (tblDealer INNER JOIN tblEvent ON tblDealer.ShopID = tblEvent.ShopID)
INNER JOIN tblShow ON tblEvent.ShowID = tblShow.ShowID
WHERE (((tblShow.EndShowDate)[In Shows after date input]))
ORDER BY tblDealer.DealerLastName;

At present I have the Contract Received field set to Date() to type it as
a date.
I want it to be typed as date/time but null so the client can type in the
date later. Is there some elegant way to do this?

That works just right, the field is date/time in the table and the value is
null. thank you,
Claudette
"Allen Browne" wrote in message
...
In general, the best approach is to set up the table exactly as you want
it, and then populate it with an Append query instead of the Make Table.

If that doesn't suit, you can try this:
IIf(False, #1/1/2000#, Null)
The False part is never true, so it will never insert the date, but in
some cases that's enough to give JET the clue as to what data type you
intend.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Claudette Hennessy" wrote in message
...
I know field formats are not inherited in a Make Table Query. Here is
the query.
SELECT DISTINCT tblDealer.DealerFirstName, tblDealer.DealerLastName,
tblDealer.Address, tblDealer.HomeTown, tblDealer.HomeState,
tblDealer.HomeZip, Date() AS [Date Mailed], Date() AS [Contract Received]
INTO tblCurrentContractList
FROM (tblDealer INNER JOIN tblEvent ON tblDealer.ShopID =
tblEvent.ShopID) INNER JOIN tblShow ON tblEvent.ShowID = tblShow.ShowID
WHERE (((tblShow.EndShowDate)[In Shows after date input]))
ORDER BY tblDealer.DealerLastName;

At present I have the Contract Received field set to Date() to type it
as a date.
I want it to be typed as date/time but null so the client can type in the
date later. Is there some elegant way to do this?