Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62

    Unanswered: Extract number from text field

    I need some help pulling a number from a text field.

    My source data looks like this:

    G123CA

    The good thing is that the CA and the G (as far as number of characters) are consistent.

    I am currently using a query to pulll the 123 out (which I do successfully). I am using right() and left() in my query for this process. However, the 123 from the query is in a text field.

    Can someone help me with making the results return in a field formatted as a number?

    Thanks,
    Jack

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    format(mid("G123CA",2,3), "000")

    Result in the debug window:

    ?223-format(mid("G123CA",2,3), "000")
    100


    You could also do , "0") if you wanted.

    You don't really need right() and left(), mid() will work just as well.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    Thanks for this. Mid is a lot faster for sure.

    However, this is still returning the numbers as text.

    Any other thoughts on this?

    Thanks,
    Jack

  4. #4
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    OK, I figured it out.

    Within the query (design view) I click on properties for the column and in the Format field I put #.

    Then when my query returns the values they are formatted as numbers.

    However ... I have an additional question.

    I am trying to link this field to another field in my database. The original number in the database was created as a Double.

    When I try to link this new extracted number to the Double within the database...I get a type mismatch error.

    Any thoughts on this?

    Many thanks,
    Jack

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by Jaash
    OK, I figured it out.

    Within the query (design view) I click on properties for the column and in the Format field I put #.

    Then when my query returns the values they are formatted as numbers.

    However ... I have an additional question.

    I am trying to link this field to another field in my database. The original number in the database was created as a Double.

    When I try to link this new extracted number to the Double within the database...I get a type mismatch error.

    Any thoughts on this?

    Many thanks,
    Jack

    Use the CDbl function to return a double. CDbl(fieldvalue)

    Gregg

Posting Permissions

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