Do you have two fields to contain the results? Can you give us a small sample
of the data and what you expect the data to be converted to?
Two fields to be populated one a number field and the second a text field:
To get a value that is of type number you would use
IIF(IsNumeric(SomeValue),Val(SomeValue),Null)
To get a value that is text, you would use
IIF(IsNumeric(SomeValue),Null,SomeValue)
If you are asking to split the data into two pieces based on number and text
in one field, then the problem may be more complex. For instance, "123 Gold
doubloons" and "Gold Doubloons: 123" can both be converted to 123 and "Gold
Doubloons" but the algorithm to do so is different. Especially if you might
have something like "Gold Doubloons: Chest 1: 123".
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
