Format Field in Make Table Query

[Claudette Hennessy]

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?

 

[Allen Browne]

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.

 

[Claudette Hennessy]

That works just right, the field is date/time in the table and the value is
null. thank you,
Claudette

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

Reply to group, rather than allenbrowne at mvps dot org.

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?