Trying to converting text field into numeric in a query

EA

unread,

Dec 14, 2006, 4:05:00 PM12/14/06

to

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!

John Spencer

unread,

Dec 14, 2006, 4:33:41 PM12/14/06

to

You can convert the numbers in a several ways. It depends on whether or not
you have a decimal portion to the number.

Val([Number]) returns a number in double format.
CInt, CDbl, CLng, CSng will all convert string numbers in there respective
ranges to Integers, Double floating point, Long integers, and Single
floating point.
CCur converts to a number also with four decimal places of accuracy.

"EA" <E...@discussions.microsoft.com> wrote in message
news:A3F86B02-636B-46A8...@microsoft.com...

Jerry Whittle

unread,

Dec 14, 2006, 4:45:01 PM12/14/06

to

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.

Jayneedshelp

unread,

Apr 19, 2007, 5:10:02 PM4/19/07

to

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

Jerry Whittle

unread,

Apr 19, 2007, 7:00:00 PM4/19/07

to

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.

John W. Vinson

unread,

Apr 19, 2007, 6:59:59 PM4/19/07

to

On Thu, 19 Apr 2007 14:10:02 -0700, Jayneedshelp
<Jaynee...@discussions.microsoft.com> 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]

Jayneedshelp

unread,

Apr 19, 2007, 7:36:00 PM4/19/07

to

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

Jayneedshelp

unread,

Apr 19, 2007, 8:14:01 PM4/19/07

to

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

unread,

Apr 20, 2007, 12:33:03 AM4/20/07

to

On Thu, 19 Apr 2007 17:14:01 -0700, Jayneedshelp
<Jaynee...@discussions.microsoft.com> 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]

Jayneedshelp

unread,

Apr 23, 2007, 4:22:02 PM4/23/07

to

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

unread,

Apr 23, 2007, 7:53:47 PM4/23/07

to

On Mon, 23 Apr 2007 13:22:02 -0700, Jayneedshelp
<Jaynee...@discussions.microsoft.com> 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]

Jayneedshelp

unread,

Apr 23, 2007, 8:58:02 PM4/23/07

to

Does that have a plug in to Excel?

John W. Vinson

unread,

Apr 24, 2007, 12:54:26 AM4/24/07

to

On Mon, 23 Apr 2007 17:58:02 -0700, Jayneedshelp
<Jaynee...@discussions.microsoft.com> 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]

E-mail report using Lotus Notes rather t

unread,

Jan 6, 2010, 6:28:01 PM1/6/10

to

Hi,

I used the CLNG([fieldName]) to change a text field into a number field.
But the text field are number format with decimals. When I convert the
field, it does not have decimal places but it rounds up for me. Is there a
way to convert to number with the decimal?

example text field: 20.1

Converted into number field: 20.1 not 20

thank you so much.

Phil Smith

unread,

Jan 6, 2010, 6:55:55 PM1/6/10

to

Don't use CLNG, it automatically rounds. Try CDBL(), as it supports
decimals.