Date or Null into datetime Field of Make-table Action Query

[JohnHarkins]

Hi!

My searching may be over. So far I've been only modestly successful, so maybe you can help me.

I want to create a table from a union query that consists of two SQL action queries. The new field is to be in datetime format and is derived from another date field in the same record. The date in the source field is in the format of mm/dd/yy and the object (destination) field is in a first-day-of-the-month format (I use a little first-of-the-month conversion function). If there's no date value (either because the original field in the table contains no value or a null value), I want to "stuff" a null into the object/destination field. In either case, I want the resulting mm/01/yy date or null to be placed into a field of the make-table table, which has a datetime format. (You knew there was a gotcha coming, didn't you!)

Do I use SQL with some special attribute that forces the object/destination field to be in datetime format? Can I use SQL to specify the format of the assignment of the source to the object/destination field?

Incidentally, the reason for my insistence on datetime format is that I want the flexibility to treat the result as a true date for sorting and other purposes. Yes, I could use a text field and reformat into YYYYMM format in order to do the sorting, but I want to be a purist about this thing.

Thanks!

John Harkins

 

[BNPMike]

Couple ofm points:

1) there's no merit in loading data with a UNION query. You'd be better loading the first set of data then loading the second, in two steps. Then you can examine what's happenning more easily, trap errors, and it'll be easier to write.

2) first create the new table yourself then you will have all the attributes you want like date/time format for the field, ie load by appending rather than make-table.

To load the special date you want, you are going to have to chop the source date into bits to get the month and year and then put it back together with a day of 01. You can do this all with SQL.