How to CLng in a SQL query.

Jayneedshelp · External Since: Apr 19, 2007 Posts: 3

I have a good query however one of the output columns I want to convert from
text to a number. I think that the CLng is the correct option I just do not
understand how to get the output to be a changed by the CLng function.

Dick Kusleika · External Since: Mar 08, 2007 Posts: 13

On Thu, 19 Apr 2007 14:02:02 -0700, Jayneedshelp
<Jayneedshelp.DeleteThis@discussions.microsoft.com> wrote:

>I have a good query however one of the output columns I want to convert from
>text to a number. I think that the CLng is the correct option I just do not
>understand how to get the output to be a changed by the CLng function.

It looks like this with a Jet (Access) database

SELECT tblLink.ProductID, CLng([MyTextField]) AS ConvertedText
FROM tblLink;
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Jayneedshelp · External Since: Apr 19, 2007 Posts: 3

Hello Dick I cannot get your suggestion to work.
I am using msquery to get data into Ecel from a Oracle database.

I an error ORA-00923 FROM keyword not found where expected.

Is the data in the ( ) renaming the outout field?

Any further help would be appreciated.

Jay

"Dick Kusleika" wrote:

> On Thu, 19 Apr 2007 14:02:02 -0700, Jayneedshelp
> <Jayneedshelp.DeleteThis@discussions.microsoft.com> wrote:
>
> >I have a good query however one of the output columns I want to convert from
> >text to a number. I think that the CLng is the correct option I just do not
> >understand how to get the output to be a changed by the CLng function.
>
> It looks like this with a Jet (Access) database
>
> SELECT tblLink.ProductID, CLng([MyTextField]) AS ConvertedText
> FROM tblLink;
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
>

Dick Kusleika · External Since: Mar 08, 2007 Posts: 13

On Mon, 23 Apr 2007 13:34:01 -0700, Jayneedshelp
<Jayneedshelp.RemoveThis@discussions.microsoft.com> wrote:

>Hello Dick I cannot get your suggestion to work.
>I am using msquery to get data into Ecel from a Oracle database.
>
>I an error ORA-00923 FROM keyword not found where expected.
>
>Is the data in the ( ) renaming the outout field?
>
>>
>> It looks like this with a Jet (Access) database
>>
>> SELECT tblLink.ProductID, CLng([MyTextField]) AS ConvertedText
>> FROM tblLink;

Jay:

CLng is a function in Jet, put I don't think it exists in Oracle. I don't
know anything about Oracle, but you might want to try the TO_NUMBER function
as described here

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/function...5a.htm#

You might try

SELECT TO_NUMBER('MyTextField') "ConvertedText" FROM MyTable

Where MyTextField is the field name you want to convert, MyTable is the name
of the table, and ConvertedText is the name you want the converted field to
be.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com