If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate Text Fields to Date Field
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. |
#2
|
|||
|
|||
Concatenate Text Fields to Date Field
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. |
#3
|
|||
|
|||
Concatenate Text Fields to Date Field
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. |
#4
|
|||
|
|||
Concatenate Text Fields to Date Field
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Concatenate Text Fields to Date Field
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 . |
Thread Tools | |
Display Modes | |
|
|