Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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))
    Regards,

    Gary
    (It's been a while!)

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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
    Regards,

    Gary
    (It's been a while!)

  7. #7
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: extract number from field (2003)

    I am getting the same error message.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: extract number from field (2003)

    Are any of the values null?
    Regards,

    Gary
    (It's been a while!)

  10. #10
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •