Prev: Word merged with Access
Next: Access 2007 Append Query � Issue with automatically inserted b
From: alhotch on 19 May 2010 11:55
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.
From: Dorian on 19 May 2010 12:02
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.
From: Jerry Whittle on 19 May 2010 12:25
Good point. I would run something like this first:

SELECT [tblTable].[fMonth], [tblTable].[fDay], [tblTable].[fYear]
FROM tblTable
WHERE IsDate([tblTable].[fMonth] & "/" &
[tblTable].[fDay] & "/" & [tblTable].[fYear]) = False

This will show any problem records.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dorian" wrote:

> 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.
From: alhotch on 19 May 2010 12:33
Thanks for the prompt reply, Dorian. The CDate statement did not work.
Neither did the "#" parameter. In both cases, the "append" did not work. No
records were "appended". However, when I use my origial SELECT statement, the
records do get updated even though I get the "type violation fialure" message.

"Dorian" wrote:

> 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.
From: Bob Barrows on 19 May 2010 12:38
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


 |  Next  |  Last
Pages: 1 2
Prev: Word merged with Access
Next: Access 2007 Append Query � Issue with automatically inserted b