Try surrounding whole thing with CDate( )
e.g.
INSERT INTO ....... SELECT CDate([tblTable].[fMonth] & "/" &
[tblTable].[fDay] &
"/" & [tblTable].[fYear]) AS fDate

or maybe try...

INSERT INTO ....... SELECT "#" & [tblTable].[fMonth] & "/" &
[tblTable].[fDay] &
"/" & [tblTable].[fYear] & "#" AS fDate

regardless it wont work if your tblTable contains null in any year/month/day
field or if any constructed date is invalid.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"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.