Creating empty date field with make-table query

  • Thread starter Thread starter TT
  • Start date Start date
T

TT

I am trying to create a new table using a make-table query
where one of the fields is formatted as a date but has no
data in it initially.
 
I can't think of a way to do this through a single query; the tough part is
defining the field as type date/time when no actual data is passed, as in a
make table query the data type is defined implicitly by the data type in the
original table / query, or the data itself if it's a calculated field.
I can offer a two-step solution, though: First run a Make-Table query and
use a calculated field for the date, using function Now() to pass the
current date/time so the field is created as type date/time field, then run
a second query, an Update one this time, on the table, to set the particular
field values to Null.

HTH,
Nikos
 
Hi TT,

just in case "TT" <> 'tt' (other post)

Another clever method Michel once demonstrated:

NewField: IIf(True,Null,#1/1/1900#)

-- you end up with date/time field, all nulls

-- works for text and number also
NewField: IIf(True,Null," ") <--get type text(255), all null
NewField: IIf(True,Null,0) <--get type Long, all null

-- or use Cxxx functions
NewField: IIf(True,Null,CCur(0)) <--get type Currency, all null
NewField: IIf(True,Null,CDbl(0)) <--get type Double, all null

So clever...and eliminate "binary type" bugaboo
when you use

NewField: Null

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Brilliant!

Gary Walter said:
Hi TT,

just in case "TT" <> 'tt' (other post)

Another clever method Michel once demonstrated:

NewField: IIf(True,Null,#1/1/1900#)

-- you end up with date/time field, all nulls

-- works for text and number also
NewField: IIf(True,Null," ") <--get type text(255), all null
NewField: IIf(True,Null,0) <--get type Long, all null

-- or use Cxxx functions
NewField: IIf(True,Null,CCur(0)) <--get type Currency, all null
NewField: IIf(True,Null,CDbl(0)) <--get type Double, all null

So clever...and eliminate "binary type" bugaboo
when you use

NewField: Null

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
Back
Top