# Thread: extract number from field (2003)

1. ## extract number from field (2003)

Hello,

I have received a database that has a field named payment and it is a text field. An example of the data in this field is \$2000 per diem. I want to be able to do calculations on this field so I only need the number part. I need to somehow extract the number out of all these fields. What would be the best way to do this? I figure the best way would be to create another field and update with the extracted number part.

2. ## Re: extract number from field (2003)

Is there any consistency in the field such as all dollar values end in .00 or there is always a space after the last number? Does the field always start with a \$ or number?

3. ## Re: extract number from field (2003)

They always start with \$number space the some type of text. I thought of doing some type of update with the Instr function.

4. ## Re: extract number from field (2003)

If the field ALWAYS starts with a number then a space, try the following in a query:

(This assumes the field in question is called Num)

IIf(InStr([Num]," ")>0,Left\$([Num],InStr([Num]," ")-1))

5. ## Re: extract number from field (2003)

Thanks Gary that works. I have an additional question though. After I have that infromation I am left with \$1000 for example. I now want to do averages off this but I get an error message saying the expression is typed incorrectly or too complex to be evaluated. Would it be the \$ sign in front that is throwing it off.

6. ## Re: extract number from field (2003)

Nope, that should work. If you want to caculate an average, try this in the query

Avg(IIf(InStr([Num]," ")>0,Left\$([Num],InStr([Num]," ")-1)))

Then in the Total area, (You will need to select totals), select expression

HTH

7. ## Re: extract number from field (2003)

I am getting the same error message.

8. ## Re: extract number from field (2003)

Does it help if you use
<code>
Left([Num],InStr([Num]," ")-1))
</code>
or
<code>
CCur(Left([Num],InStr([Num]," ")-1)))
</code>
Note the use of Left instead of Left\$, and the absence of IIf.

9. ## Re: extract number from field (2003)

Are any of the values null?

10. ## Re: extract number from field (2003)

No null values but what I have done is converted that number and named the field rate. I never could AVG the rate but I made that query a make table query and named the table facility rates then queried off that table to get the average. That seems to work.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•