Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Truncating Text from a field (2000)

    Hi,
    I have a field that starts with a number, then has a space, then text. I want to get rid of the text so that field can be converted to a number field. Example. "1,056 hrs" or "96 hrs". I know about the Left or Right functions, but I don't want to keep the "hrs" I want to delete it from the field. The left function won't work because the length of the number varies. Is there a way I can say delete last 4 characters of the field?

    I am importing an Excel spreadsheet that was generated from a MS Project file that contains the Task Name and Remaining Work Hours. I can't figure out in Project how to get rid of the "hrs" so I figured I could do it easier in Access.

    Thanks in advance.
    Deb Buck

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Truncating Text from a field (2000)

    You can use the Left function with Len function, like this:

    ? Left("1,045 hrs", Len("1,045 hrs")-4)
    1,045

    But Left will always return a string:

    ? Left("1,045 hrs", Len("1,045 hrs")-4)+ Left("96 hrs", Len("96 hrs")-4)
    1,04596

    An alternative would be use Val function with Replace function (to get rid of the commas):

    ? Val(Replace("1,045 hrs",",","",,,vbBinaryCompare))
    1045
    ? Val(Replace("096 hrs",",","",,,vbBinaryCompare))
    96

    Reason why you need to get rid of commas:

    ? Val("96 hrs")
    96
    ? Val("1,045 hrs")
    1

    That is, Val will stop at comma & return 1, not 1045. Val function returns a number, not string:

    ? Val(Replace("1,045 hrs",",","",,,vbBinaryCompare))+ Val(Replace("96 hrs",",","",,,vbBinaryCompare))
    1141

    Note in above example, the 2 numbers were added, not concatenated, as with the Left example above. If the data being imported is all in same format as examples you provided, recommend use Val & Replace to clean up numbers. Replace literals in example with name of field in query. Note that on some A2K systems Replace function does not work in a query expression, you may have to wrap it in a user-defined function to use in query, such as an update query.

    PS - If using expression in query, also replace "vbBinaryCompare" constant with numerical value (0) - constant names aren't recognized when used in query expressions.

    HTH

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Truncating Text from a field (2000)

    Mark,
    Thanks for the reponse. Right now I got the Left, Len to work. I couldn't get the Val, Replace to work, but I have a question in to make sure we have the correct service packs and all. But I have some questions on that statement because I don't quite understand everything in it. More detail would be appreciated. Also, I'm not combining the numbers, I just need each record to be a number.

    With this statement -- ? Val(Replace("1,045 hrs",",","",,,vbBinaryCompare)) --
    1. What's the ? for?
    2. If I'm using the field name of RemainingWork, do I still need the " or do I use [.
    3. Why so many " and , after the name "1,045 hrs",",","",,,

    Thanks,
    Deb
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Truncating Text from a field (2000)

    <P ID="edit" class=small>(Edited by MarkD on 04-Sep-03 21:04. Fixed typo
    in sample code.
    )</P>The question mark (?) was copied from VB Editor Immediate ("Debug") window. It's shorthand for Debug.Print. The idea was to illustrate some quick examples of how to convert the data from text to numbers using various functions. If using field name, do not use quotes, use syntax like [FieldName]. In Replace function, the 2nd & 3rd arguments ("find" & "replace") require you to specify the substring to search for, and replacement text, respectively. Unless you are using a variable in code, or referencing a field in a query, these will be represented as literal text and therefore delimited by the quote marks. The commas are for optional arguments, which do not need to be specified, you need to include the comma so VBA will "skip" that argument. (I included the last optional argument, "compare" type, because a binary comparison will often execute faster than a textual comparison.) For more info on Replace function syntax, look up "Replace" in VBA Help.

    I combined the expressions in previous reply only to illustrate the difference between a function that returns a text string and one that returns a numerical value.

    Since Replace doesn't work (it should be available in A2K or later), recommend stick with Left function. The resulting text can be converted to a number using a conversion function, such as CLng (convert to Long Integer) or CDbl (convert to a Double number). I don't know how the number field is defined in your table, if it will always be a whole number, it should be defined as Integer or Long (use CInt or CLng to convert), if can be fractional, define as Double and use CDbl to convert. These functions are preferable to Val in any event.

    Examples:

    ? CLng(Left("1,045 hrs",Len("1,045 hrs")-4))
    1045

    ? CDbl(Left("1,045.5 hrs",Len("1,045.5 hrs")-4))
    1045.5

    You should have no problem using one of the above examples in an update query. Just replace the literal value with the field name:

    CLng(Left([RemainingWork ],Len([RemainingWork])-4))

    HTH

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Truncating Text from a field (2000)

    I don't know if you've solved your problem yet, but if the text that you want to strip is consistently " hrs", you could just use a good, old-fashioned find and replace from the edit menu on the table. Type " hrs" into the find box and replace with nothing. If there are commas in the numbers, you can do the same thing with them.

    It's low-tech, but it is quick and easy. I'd suggest working on a copy as find and replace isn't undoable.

    PeterN

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Truncating Text from a field (2000)

    Thanks a ton.

    Deb

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Truncating Text from a field (2000)

    The Left function with the Len function worked, but I never thought of the old Find and Replace, sometimes we make it harder, but I am also trying to automate it.

    Thanks,
    Deb

Posting Permissions

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