Prev: Word merged with Access
Next: Access 2007 Append Query � Issue with automatically inserted b
From: alhotch on 19 May 2010 13:43
Thanks to all of you for your input. I have found that one of the records
that I am trying to "append" has "zero" values in the fMonth, fDay, and fYear
fields. When I correct the invalid values, my original INSERT INTO query
works. Jerry Whittle's reply would catch these "zero" value entries.

I'm back in business. Thanks for enlightening me on the options to use
CDate, DateSetial, and IsDate functions in my expressions. You ALWAYS learn
something new when working with these forums.

"Bob Barrows" wrote:

> 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
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Word merged with Access
Next: Access 2007 Append Query � Issue with automatically inserted b