Function to change text to numbers?

[tom]

Can someone be so kind as to advise the function that
changes text to numbers? If there is such.

 

[Rick B]

What do you mean? Are you trying to change a text field into a numeric
field so you can perform math on it? If so, does the field only contain
numbers?

 

[JK]

Tom,

Val(YourSring) will return a number.

If the whole string looks like a number, you get the number if it has non
numeric characters, the string is evaluate (from left to right) until the
first non numeric character, although it ignores spaces:

Val("1234")=1234
Val("12dg46")=12
val("12 345 6")=123456

Regards/JK

 

[Roger Carlson]

Depending on what type of number you wanted your text converted to, you'd
use one of these:

CBool() - Boolean
CByte-Byte 0 to 255.
CCur() - Currency
CDate() - Date
CDbl() - Double
CDec() - Decimal
CInt() - Integer
CLng() - Long Integer
CSng() - Single
CVar() - Variant

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jamie Collins]

Depending on what type of number you wanted your text converted to, you'd
use one of these:

CBool() - Boolean
CByte-Byte 0 to 255.
CCur() - Currency
CDate() - Date
CDbl() - Double
CDec() - Decimal
CInt() - Integer
CLng() - Long Integer
CSng() - Single
CVar() - Variant

In Jet SQL, CVAR() will have no effect and CDEC() is broken (returns
either a zero or an error). CDATE() returns a DATETIME value, which is
not a number e.g.

SELECT ISNUMERIC(CDATE("39.022E3"))

Jamie.

--

 

[Roger Carlson]

1. DateTime field IS a number. It is a double-precision number where the
integer portion represents the date and the decimal portion represents the
time. For example:

SELECT CDate("31111.52421") AS Expr1;
will return:
3/5/1985 12:34:52 PM

SELECT CDate("31111.52421") + 1 AS Expr1;
will return:
3/6/1985 12:34:52 PM

Granted, it is not the most likely case, but I included it for completeness.
As for IsNumeric, the DateTime datatype is a special case. Even though it
is a number, IsNumeric will return false, probably because most users don't
think of a date as a number, even though it is stored as one.

2. CVAR certainly works in SQL:

SELECT CVar("2.5")+1 AS Expr1;
returns
3.5

The value is held in its original format (text in this case) until a math
operation is performed on it. So simply using the CVar by itself will not
convert the text to numbers, but if you do any math on it, it will. Again,
not the most common case, but it does convert text to a number.

3. CDEC does not in fact work in SQL queries
(http://support.microsoft.com/kb/225931). However, I don't see anything in
the orignal question that specified SQL. CDec() certainly works in VB code.

Debug.Print CDec("2.4") + CDec("3.2")
returns
5.6

 

[Jamie Collins]

DateTime field IS a number.

SELECT CDate("31111.52421") AS Expr1;
will return:
3/5/1985 12:34:52 PM

It returns a DATETIME value.

SELECT CDate("31111.52421") + 1 AS Expr1;
will return:
3/6/1985 12:34:52 PM

It returns a DATETIME value, demonstrating that integer arithmetic on a
DATETIME returns a DATETIME.

Sorry, I missed your point. Why exactly does that make a DATETIME value
a number?

If your query added 1.1, the result would be DECIMAL but the fact a
DATETIME can be coerced to (or cast as) a numeric type does not make it
a numeric value, IMO.

As for IsNumeric, the DateTime datatype is a special case. Even though it
is a number, IsNumeric will return false, probably because most users don't
think of a date as a number

I'm not convinced. I don't suppose you have a citation for this
'special case'...?

CVAR certainly works in SQL:

SELECT CVar("2.5")+1 AS Expr1;
returns
3.5

The value is held in its original format (text in this case) until a math
operation is performed on it. So simply using the CVar by itself will not
convert the text to numbers, but if you do any math on it, it will. Again,
not the most common case, but it does convert text to a number.

Again, I'm not convinced.

Considering

SELECT TYPENAME(CVAR('1'))

returns 'String' and

SELECT '1' + 1

returns 2, why do you think CVAR has *any* effect?

3. CDEC does not in fact work in SQL queries
However, I don't see anything in
the orignal question that specified SQL.

I don't see anything in the orignal question that excludes SQL.

Jamie.

--

 

[Jamie Collins]

DateTime field IS a number. It is a double-precision number where the
integer portion represents the date and the decimal portion represents the
time.

Allow me to try to convince you otherwise.

Using Northwind:

SELECT OrderID, OrderDate
INTO [Text;DATABASE=C:\;].[deleteme#txt]
FROM Orders;

Open the resulting file (C:\deleteme.txt) and note the OrderDate values
are store as text, not stored as double float values, etc.

Now, query the data in the text file:

SELECT DISTINCT TYPENAME(OrderDate)
FROM [Text;DATABASE=C:\;].[deleteme#txt];

returns 'Date'.

In summary, we have values *physically* stored as text that in Jet SQL
become *conceptual* DATETIME values. At no point are the values
considered 'numeric'.

The MDB format may physically store them as double float but same
reasoning applies i.e. conceptually they are DATETIME values, not
'numeric' values.

Jamie.

--

 

[Roger Carlson]

1) Date/Time Data Type
From Access Help:
---------
Date Data Type:
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999 and
times from 0:00:00 to 23:59:59. Any recognizable literal date values can be
assigned to Date variables. Date literals must be enclosed within number
signs (#), for example, #January 1, 1993# or #1 Jan 93#.
Date variables display dates according to the short date format recognized
by your computer. Times display according to the time format (either 12-hour
or 24-hour) recognized by your computer.

When other numeric types are converted to Date, values to the left of the
decimal represent date information while values to the right of the decimal
represent time. Midnight is 0 and midday is 0.5. Negative whole numbers
represent dates before 30 December 1899.
---------
Therefore Dates ARE numeric (floating-point number).

2) IsNumeric()
From Access Help:
--------
IsNumeric Function
Returns a Boolean value indicating whether an expression can be evaluated as
a number.
Syntax
IsNumeric(expression)
The required expression argument is a Variant containing a numeric
expression or string expression.
Remarks
IsNumeric returns True if the entire expression is recognized as a number;
otherwise, it returns False.
IsNumeric returns False if expression is a date expression.
--------
If a date was NOT a numeric, the second statement would be redundant because
the first remark would cover it. Therefore it is a special case where it
returns False for a numeric expression.

3) CVar and CDec
Just because SQL casts a string into a number correctly when used in a math
expression does not mean that CVar does not.

Regardless, my comment about the original question not being exclusive to
SQL stands for both. The original question asked what function would convert
text to numeric. These are both functions that convert text to numeric.

 

[Jamie Collins]

Dates ARE numeric (floating-point number).

I think you are confusing the logical with the physical storage and in
doing so I think you miss the point of SQL's strong data typing

Just because SQL casts a string into a number correctly when used in a math
expression does not mean that CVar does not.

I can't see why you would expect CVAR() to convert text values to
numeric values.

In VBA I would expect CVar(<string value>) to always return a Variant
type with a sub type of String, even if the string value could be cast
as numeric.

I am not aware of Jet having a Variant data type, therefore I cannot
imagine that CVAR could do anything. If anyone has evidence to the
contrary I'd appreciate them posting it here.

What we have seen in this thread so far proves nothing either way, IMO.
Consider:

SELECT 1 + 1;
SELECT CVAR('1') + 1;
SELECT '1' + 1;

The above all return the same result of 2 (INTEGER). Therefore, this
provides no proof that CVAR('1') has or has not cast the text as a
numeric. Do you agree?

Further consider that:

SELECT TYPENAME(CVAR('1'))

returns 'String', which for me proves that CVAR has not cast the text
as numeric. Please explain why you think it has.

Jamie.

--

 

[Roger Carlson]

I'll repeat this once and will not respond again.

The original poster asked about functions that convert text to numbers. I
gave functions that did so. This nit-picking is hardly productive use of
either or our time.

 

[Jamie Collins]

The original poster asked about functions that convert text to numbers. I
gave functions that did so.

Yes, but you included some that don't.

This nit-picking is hardly productive use of
either or our time.

I don't mind putting the extra time so that readers including (but not
limited to) the OP may benefit. I sorry you see me pointing out the
inaccuracies in your proposed solution as 'nit picking'

Jamie.

--