A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Concatenate Text Fields to Date Field



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 04:55 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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  
Old May 19th, 2010, 05:02 PM posted to microsoft.public.access.queries
Dorian
external usenet poster
 
Posts: 542
Default 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  
Old May 19th, 2010, 05:25 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old May 19th, 2010, 05:33 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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  
Old May 19th, 2010, 05:38 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default 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  
Old May 19th, 2010, 06:43 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.