Convert text to number

[Surf_gal]

I have a table that was uploaded into a table but one column has a field of numbers (no alphas) with a data type of "text" instead of "number". Is there a way to convert it to a number data type? Will switching the data type to number do the trick or will that cause a problem? Or is there a function I can use in a query (i.e the value function in Excel converts text fields)? Any help would be greatle appreciated.

 

[NDLINDQUIST]

You might be able to use the N() Function to convert the text to numeric values. After applying N(), you can copy and paste special the values to get the numbers you desire.

 

[Surf_gal]

N() is a function? So in a query, can I use it in creating a field like

NUM: n([PS_ZY_VESTING_TBL - DEV]![GRANT_NBR])

 

[spiderrasmon]

in your table, create a new table column for the numeric value of your data, then save & close.

then, run an update query that updates the new column to Val(expr), which converts your string expression to a numeric value...

 

[Surf_gal]

Thanks so much!

 

[NateO]

Also of potential interest are the conversion functions, e.g., CCur([myTable.Field1])

 

[Dugantrain]

Another function of interest might be Isnumeric() to test whether or not a string qualifies as a number. You can use this before you actually do the conversion so that you don't receive errors:

Iif(IsNumeric([Your_Field]),Val([Your_Field]),Null)

Also of potential interest are the conversion functions, e.g., CCur([myTable.Field1])

Nate, I've never come across this function and it's not included in the VBA help files. What does this function do?

*edit* Never mind, I see that it converts to Currency

 

[NateO]

Nate, I've never come across this function and it's not included in the VBA help files. What does this function do?

*edit* Never mind, I see that it converts to Currency

It strikes me as odd that you won't find them in the front-end help of Access, but the VBE help files do cover 'Conversion Functions.'

I have found them to be useful in the past with my recordsets, and you can use others, e.g., CLng, CDbl, etc...