How can I convert text to number

[fmaca]

Good afternoon all.

I have field that has information like this:

2
4
5
2
cn
*
1

etc. formatted as text.

As part of a query, I need to do the following:

The numbers must be numbers, hopefully as currency. The "cn" and any other text must be ignored EXCEPT the "*" which needs to have a formula applied to it.

If I change the data type to "number", I lose all the "cn" which is good, but I also lose the "*" which is bad.

Can I set up a calculated column in my query to clean this up?

Any ideas?

Thanks in advance.

Ethan

 

[scriverb]

You cannot mix and match numbers and alphabetic and special characters in the same column unless the column has a text or variant type. It is either numbers or other. Now we can create three columns numbers in one, text in the other and *'s in the other. Then you can run calculations and use the data as needed.

If I am as confused as your data please get back with me and straighten me out.

Bob Scriver

 

[GingerR]

in your query, put a new column:

Expr1: IIf(IsNumeric([Field]),CCur([Field]),IIf([Field]="*",[Put Your Formula Here],Null))

and in the criteria of this column put

Is Not Null

-----------------------------------------
substitute [Field] with your field name
ccur = function Convert to Currency you can find it in HELP