Concatenate Text Fields to Date Field
alhotch wrote:
I have three (3) columns in a table which represent the month, day,
and year. The values in these columns are 04 (month) 21 (day) 2010
(year). I want to insert these thre values into another table where
the field in the record is a Date/Time type. Here's the SQL statement
(in part):
INSERT INTO ....... SELECT [tblTable].[fMonth] & "/" &
[tblTable].[fDay] & "/" & [tblTable].[fYear] AS fDate
I get an error about this "append" that complains about "... set 1
fields(s) to Null due to type conversion failure, and it didn't add 0
record(s) the the table ..." However, the value gets entered into the
destination table.
The field type for fDate is Date/Time. The three fileds for month,
day, year, are text. The concatination works in that the string is
together but I want to correct the "type conversion failure" error.
Try DateSerial instead:
INSERT INTO ....... SELECT
DateSerial([tblTable].[fYear],[tblTable].[fMonth] , [tblTable].[fDay])
AS fDate
Test by running it without the INSERT part to make sure valid dates are
being created. There may be data in one of the rows that makes it
impossible to create a date.
--
HTH,
Bob Barrows
|