PC Review


Reply
Thread Tools Rate Thread

changing data types

 
 
Dabbler
Guest
Posts: n/a
 
      30th Jan 2004
Hi,
I have an external link to a text file. A date field is
in this format: 20040215

If during the link process I set the field as date/time, I
get an error in the resulting table, something like #num!

If I set it as TEXT, then it appears in the table, but I
am stuck with it as text type. Since it is a linked
table, I cannot change the data type after the fact.

So how do I use this field in a date-friendly manner in
queries and reports? Is there a way to programmatically
get this into another field or table as a date?

Thanks
 
Reply With Quote
 
 
 
 
Joe Fallon
Guest
Posts: n/a
 
      30th Jan 2004
I personally do not like linking to non-database files.
I import them.

But the idea would be the same because during the import (to a temp table)
you would need to keep that field as TEXT so that it comes in correctly.

Then you write an append query to move the data to the real table.
The query uses format or expressions to massage the raw data and append it
correctly.

So your queries should break up that text value into its Date equivalent.
Use DateSerial function and ocmbine each piece of the Text value into the
correct parameter.
--
Joe Fallon
Access MVP



"Dabbler" <(E-Mail Removed)> wrote in message
news:726e01c3e6c8$78f3ff10$(E-Mail Removed)...
> Hi,
> I have an external link to a text file. A date field is
> in this format: 20040215
>
> If during the link process I set the field as date/time, I
> get an error in the resulting table, something like #num!
>
> If I set it as TEXT, then it appears in the table, but I
> am stuck with it as text type. Since it is a linked
> table, I cannot change the data type after the fact.
>
> So how do I use this field in a date-friendly manner in
> queries and reports? Is there a way to programmatically
> get this into another field or table as a date?
>
> Thanks



 
Reply With Quote
 
 
 
 
Van T. Dinh
Guest
Posts: n/a
 
      30th Jan 2004
You can use the expression:

DateSerial( CInt(Left([DateText], 4)), _
CInt(Mid([DateText], 5, 2)), _
CInt(Right([DateText], 2)) )

to convert your DateText to a DateTime value.

--
HTH
Van T. Dinh
MVP (Access)



"Dabbler" <(E-Mail Removed)> wrote in message
news:726e01c3e6c8$78f3ff10$(E-Mail Removed)...
> Hi,
> I have an external link to a text file. A date field is
> in this format: 20040215
>
> If during the link process I set the field as date/time, I
> get an error in the resulting table, something like #num!
>
> If I set it as TEXT, then it appears in the table, but I
> am stuck with it as text type. Since it is a linked
> table, I cannot change the data type after the fact.
>
> So how do I use this field in a date-friendly manner in
> queries and reports? Is there a way to programmatically
> get this into another field or table as a date?
>
> Thanks



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" roadsidetree Microsoft Excel Charting 17 12th May 2010 06:51 PM
append parameter - data types in adp that correspond to sql data types Keith G Hicks Microsoft Access ADP SQL Server 1 5th Nov 2007 11:56 PM
OracleDataAdapter.Fill not mapping Oracle data types to .NET data types news.microsoft.com Microsoft ADO .NET 9 13th Oct 2005 08:01 PM
VB.NET Data Types = SQL Server Native Types Rocky Microsoft Dot NET 0 29th Sep 2004 04:49 PM
convert .NET data types to SQL data types Bamse Microsoft ADO .NET 3 2nd Jun 2004 04:24 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:29 AM.