How do you use this in the SQL query so the output data is a number?

"Jerry Whittle" wrote:

> You can use the something like the CDbl or Val functions. CDbl changes the
> text to a number. Val changes any leading characters that are numerical to a
> number. See examples below.
>
> CDbl("123") = 123
> CDbl("123.1") = 123.1
> Val("123.1") = 123.1
> Val("123.1a") = 123.1
> Val("12a3.1") = 12
>
> The CDbl will choke on data that can't be evaluated as a number or nulls.
> CDbl("123a") = Type mismatch error.
> CDbl(null) = invalid use of null
>
> Therefore you might first check your data with the IsNumeric function.
> IsNumeric(null) = false
> IsNumeric("12 3") = false
> IsNumeric("12A3") = false
> IsNumeric("123") = true
> --
> Jerry Whittle
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "EA" wrote:
>
> > I have a query that is querying a text field from a table but the text values
> > are numbers. Is there any way I can setup my query so that they query will
> > interpret and return the text field as a numeric field?
> >
> > Thanks!

On Thu, 19 Apr 2007 14:10:02 -0700, Jayneedshelp
<(E-Mail Removed)> wrote:

>How do you use this in the SQL query so the output data is a number?
>
>"Jerry Whittle" wrote:
>
>> You can use the something like the CDbl or Val functions. CDbl changes the
>> text to a number. Val changes any leading characters that are numerical to a
>> number. See examples below.
>>
>> CDbl("123") = 123
>> CDbl("123.1") = 123.1
>> Val("123.1") = 123.1
>> Val("123.1a") = 123.1
>> Val("12a3.1") = 12


Put your choice of the expressions (or use CLng or CInt for whole numbers) in
a vacant Field cell in the query grid.

John W. Vinson [MVP]

Good question. Up in the field names in QBE grid you would change the field
name by surrounding it with the function.

Say that the field name is [StreetNumber], you would change it to
CLng([StreetNumber]). Now Access will make it look something like:

Expr1: CLng([StreetNumber])

You can change the Expr1 part before the colon to something like :
StreetNum: CLng([StreetNumber])

Now for checking that the number can be changed to a number, put something
like this in another field:

IsNumeric([StreetNumber])

In the Criteria put Yes or -1. The Yes should not have quotation marks
around it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jayneedshelp" wrote:

> How do you use this in the SQL query so the output data is a number?
>
> "Jerry Whittle" wrote:
>
> > You can use the something like the CDbl or Val functions. CDbl changes the
> > text to a number. Val changes any leading characters that are numerical to a
> > number. See examples below.
> >
> > CDbl("123") = 123
> > CDbl("123.1") = 123.1
> > Val("123.1") = 123.1
> > Val("123.1a") = 123.1
> > Val("12a3.1") = 12
> >
> > The CDbl will choke on data that can't be evaluated as a number or nulls.
> > CDbl("123a") = Type mismatch error.
> > CDbl(null) = invalid use of null
> >
> > Therefore you might first check your data with the IsNumeric function.
> > IsNumeric(null) = false
> > IsNumeric("12 3") = false
> > IsNumeric("12A3") = false
> > IsNumeric("123") = true
> > --
> > Jerry Whittle
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "EA" wrote:
> >
> > > I have a query that is querying a text field from a table but the text values
> > > are numbers. Is there any way I can setup my query so that they query will
> > > interpret and return the text field as a numeric field?
> > >
> > > Thanks!

In my SQL query how would I change the
Query is listed below.
TASK_VARIABLES_OCCURANCES.VARIABLEVALUE into a number?
It is stored as text in Oracle I know the data really represents a number.
I am not worring about testing the data.

I do not know what a QBE grid is. I have been using MSQuery as a query
editing tool. I can modify the query directly.

SELECT TSKVAROCALINE.NAME, TASK_VARIABLES_OCCURRENCES.NAME,
TASK_VARIABLES_OCCURRENCES.VARIABLEVALUE, TASK.CREATOR,
TASK_VARIABLES_OCCURRENCES.VARIABLETIME
FROM OPS$OCSHIS.TASK TASK, OPS$OCSHIS.TASK_VARIABLES_OCCURRENCES
TASK_VARIABLES_OCCURRENCES, OPS$OCSHIS.TSKVAROCALINE TSKVAROCALINE
WHERE TASK.TASKID = TASK_VARIABLES_OCCURRENCES.TASKID AND
TSKVAROCALINE.VARIABLEVALUE = TASK.CREATOR AND
((TASK_VARIABLES_OCCURRENCES.NAME Like '%tt') AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?) OR
(TASK_VARIABLES_OCCURRENCES.NAME In ('CL2 Used','PVC Used')) AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?))
ORDER BY TASK.CREATOR


"Jerry Whittle" wrote:

> Good question. Up in the field names in QBE grid you would change the field
> name by surrounding it with the function.
>
> Say that the field name is [StreetNumber], you would change it to
> CLng([StreetNumber]). Now Access will make it look something like:
>
> Expr1: CLng([StreetNumber])
>
> You can change the Expr1 part before the colon to something like :
> StreetNum: CLng([StreetNumber])
>
> Now for checking that the number can be changed to a number, put something
> like this in another field:
>
> IsNumeric([StreetNumber])
>
> In the Criteria put Yes or -1. The Yes should not have quotation marks
> around it.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Jayneedshelp" wrote:
>
> > How do you use this in the SQL query so the output data is a number?
> >
> > "Jerry Whittle" wrote:
> >
> > > You can use the something like the CDbl or Val functions. CDbl changes the
> > > text to a number. Val changes any leading characters that are numerical to a
> > > number. See examples below.
> > >
> > > CDbl("123") = 123
> > > CDbl("123.1") = 123.1
> > > Val("123.1") = 123.1
> > > Val("123.1a") = 123.1
> > > Val("12a3.1") = 12
> > >
> > > The CDbl will choke on data that can't be evaluated as a number or nulls.
> > > CDbl("123a") = Type mismatch error.
> > > CDbl(null) = invalid use of null
> > >
> > > Therefore you might first check your data with the IsNumeric function.
> > > IsNumeric(null) = false
> > > IsNumeric("12 3") = false
> > > IsNumeric("12A3") = false
> > > IsNumeric("123") = true
> > > --
> > > Jerry Whittle
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > >
> > > "EA" wrote:
> > >
> > > > I have a query that is querying a text field from a table but the text values
> > > > are numbers. Is there any way I can setup my query so that they query will
> > > > interpret and return the text field as a numeric field?
> > > >
> > > > Thanks!

Hi John,
I do not understand your suggestion.

In my SQL query how would I change the
TASK_VARIABLES_OCCURANCES.VARIABLEVALUE into a number?

I am using MSquery to build the query. I can add a field or rename a field
if that would help.

Thanks in advance

SELECT TSKVAROCALINE.NAME, TASK_VARIABLES_OCCURRENCES.NAME,
TASK_VARIABLES_OCCURRENCES.VARIABLEVALUE, TASK.CREATOR,
TASK_VARIABLES_OCCURRENCES.VARIABLETIME
FROM OPS$OCSHIS.TASK TASK, OPS$OCSHIS.TASK_VARIABLES_OCCURRENCES
TASK_VARIABLES_OCCURRENCES, OPS$OCSHIS.TSKVAROCALINE TSKVAROCALINE
WHERE TASK.TASKID = TASK_VARIABLES_OCCURRENCES.TASKID AND
TSKVAROCALINE.VARIABLEVALUE = TASK.CREATOR AND
((TASK_VARIABLES_OCCURRENCES.NAME Like '%tt') AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?) OR
(TASK_VARIABLES_OCCURRENCES.NAME In ('CL2 Used','PVC Used')) AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?))
ORDER BY TASK.CREATOR

"John W. Vinson" wrote:

> On Thu, 19 Apr 2007 14:10:02 -0700, Jayneedshelp
> <(E-Mail Removed)> wrote:
>
> >How do you use this in the SQL query so the output data is a number?
> >
> >"Jerry Whittle" wrote:
> >
> >> You can use the something like the CDbl or Val functions. CDbl changes the
> >> text to a number. Val changes any leading characters that are numerical to a
> >> number. See examples below.
> >>
> >> CDbl("123") = 123
> >> CDbl("123.1") = 123.1
> >> Val("123.1") = 123.1
> >> Val("123.1a") = 123.1
> >> Val("12a3.1") = 12
>
>
> Put your choice of the expressions (or use CLng or CInt for whole numbers) in
> a vacant Field cell in the query grid.
>
> John W. Vinson [MVP]
>

On Thu, 19 Apr 2007 17:14:01 -0700, Jayneedshelp
<(E-Mail Removed)> wrote:

>Hi John,
>I do not understand your suggestion.
>
>In my SQL query how would I change the
>TASK_VARIABLES_OCCURANCES.VARIABLEVALUE into a number?

SELECT TSKVAROCALINE.NAME, TASK_VARIABLES_OCCURRENCES.NAME,
CLng(TASK_VARIABLES_OCCURRENCES.VARIABLEVALUE) AS VValue, TASK.CREATOR,
TASK_VARIABLES_OCCURRENCES.VARIABLETIME
FROM OPS$OCSHIS.TASK TASK, OPS$OCSHIS.TASK_VARIABLES_OCCURRENCES
TASK_VARIABLES_OCCURRENCES, OPS$OCSHIS.TSKVAROCALINE TSKVAROCALINE
WHERE TASK.TASKID = TASK_VARIABLES_OCCURRENCES.TASKID AND
TSKVAROCALINE.VARIABLEVALUE = TASK.CREATOR AND
((TASK_VARIABLES_OCCURRENCES.NAME Like '%tt') AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?) OR
(TASK_VARIABLES_OCCURRENCES.NAME In ('CL2 Used','PVC Used')) AND
(TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?))
ORDER BY TASK.CREATOR

John W. Vinson [MVP]

Hello John,

I tried your suggestion and the reply I get is. ORA-00923: FROM keyword not
found where expected.

I am querying a Oracle database.
Any other suggestions?


"John W. Vinson" wrote:

> On Thu, 19 Apr 2007 17:14:01 -0700, Jayneedshelp
> <(E-Mail Removed)> wrote:
>
> >Hi John,
> >I do not understand your suggestion.
> >
> >In my SQL query how would I change the
> >TASK_VARIABLES_OCCURANCES.VARIABLEVALUE into a number?
>
> SELECT TSKVAROCALINE.NAME, TASK_VARIABLES_OCCURRENCES.NAME,
> CLng(TASK_VARIABLES_OCCURRENCES.VARIABLEVALUE) AS VValue, TASK.CREATOR,
> TASK_VARIABLES_OCCURRENCES.VARIABLETIME
> FROM OPS$OCSHIS.TASK TASK, OPS$OCSHIS.TASK_VARIABLES_OCCURRENCES
> TASK_VARIABLES_OCCURRENCES, OPS$OCSHIS.TSKVAROCALINE TSKVAROCALINE
> WHERE TASK.TASKID = TASK_VARIABLES_OCCURRENCES.TASKID AND
> TSKVAROCALINE.VARIABLEVALUE = TASK.CREATOR AND
> ((TASK_VARIABLES_OCCURRENCES.NAME Like '%tt') AND
> (TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
> TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?) OR
> (TASK_VARIABLES_OCCURRENCES.NAME In ('CL2 Used','PVC Used')) AND
> (TASK_VARIABLES_OCCURRENCES.VARIABLETIME>? And
> TASK_VARIABLES_OCCURRENCES.VARIABLETIME<?))
> ORDER BY TASK.CREATOR
>
> John W. Vinson [MVP]
>

On Mon, 23 Apr 2007 13:22:02 -0700, Jayneedshelp
<(E-Mail Removed)> wrote:

>Hello John,
>
>I tried your suggestion and the reply I get is. ORA-00923: FROM keyword not
>found where expected.
>
>I am querying a Oracle database.

ummmmmm....

Thanks. You didn't say that, and my telepathy was on the blink.

>Any other suggestions?

Build the query using Oracle PL-SQL (which I don't remember over the ten or
twelve years since I've used it).

John W. Vinson [MVP]

Does that have a plug in to Excel?

"John W. Vinson" wrote:

> On Mon, 23 Apr 2007 13:22:02 -0700, Jayneedshelp
> <(E-Mail Removed)> wrote:
>
> >Hello John,
> >
> >I tried your suggestion and the reply I get is. ORA-00923: FROM keyword not
> >found where expected.
> >
> >I am querying a Oracle database.
>
> ummmmmm....
>
> Thanks. You didn't say that, and my telepathy was on the blink.
>
> >Any other suggestions?
>
> Build the query using Oracle PL-SQL (which I don't remember over the ten or
> twelve years since I've used it).
>
> John W. Vinson [MVP]
>

On Mon, 23 Apr 2007 17:58:02 -0700, Jayneedshelp
<(E-Mail Removed)> wrote:

>Does that have a plug in to Excel?

I'm not expert in Excel, and even less so in PL-SQL... sorry!

John W. Vinson [MVP]