how to covert text field to number in Access SQL 1

[MitchPitt]

I am trying to convert a text field to a number field. We have a data warehouse, and the table in the warehouse stores number values as text (Why?! no clue!). How can i change the value type on the fly in an Access SQL query? I've tried the CDbl function since the values have 2 decimal place, but I have not had any success with it. Suggestions?

 

[ProgramError]

dim newstring as string

...

newstring = str(thenumber)

Program Error
Programmers do it one finger at a time!

 

[pbrodsky]

The Val function

as in
SELECT Val([txtval]) AS x1
FROM tbl1;

 

[MitchPitt]

Thank you pbrodsky!

 

[BarryBolin]

If you look at this bit of code it both adds 2 string values together and converts them to a decimal point value "double"
If you just want to convert a string and can be sure it will be numeric then just use the fomat part of this utility.
CDbl(rsMembers!Your_Field+ Format(rsMembers!month_max_untax_mult, "##,##0.00") / 100)

I would suggest that you include the function IsNumeric( Your_Field) to ensure that the value is numeric. this could be interesting.... Read FUN.

 

[fburges]

Have you tried multiplying your text value by 1 in a query? This simple approach works for me. It causes Access SQL to coerce the text value into a numeric value. Example: numericvalue = [textvalue] x 1. Here is some example SQL for your query:
SELECT Mytable.TEXTNUMBER, [TEXTNUMBER]*1 AS NUMNUMBER
FROM Mytable;
In this example, Mytable.TEXTNUMBER contains a number as text(field data type = Text). As text, the number aligns to the left side of TEXTNUMBER. After the multiplication by 1, a temporary field named NUMNUMBER winds up containing the same value, but it is a number now and aligns to the right, as numbers do.