Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Characters from Numeric Strings (97)

    Hi, in a query I am trying to write an expression which deletes the last two digits from a variable length numeric string - I know there's a function out there somewhere, but can find it........... Can anyone help??? Thanks!

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Characters from Numeric Strings (97)

    Well if is a true string then you can do this:

    strNumber = left$(strNumber, len(strNumber)-2)
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Characters from Numeric Strings (97)

    Hi thanks very much for help. I'm sorry to be obtuse.... its that Friday afternoon feeling....(well, daily feeling in my case!) What I am trying to do is this - I have an inventory id number field which may vary from 3 to however many in length. Taking away the last two digits of this ID gives me another code which I need to use as a link to another table. I have been trying Left(([Inventory_Id],4)) but obviously this doesn't allow for the variable length of the string. Can you tell me how I would fit the Len function into this scenario?

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Characters from Numeric Strings (97)

    Hello again - having slapped myself around head very hard indeed, now see what you were trying to tell me! thanks very much worked a treat!

    Marion

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Characters from Numeric Strings (97)

    Based on the fact that you [] around the Field name, it looks like you are doing this in a Query, correct?

    If so, you will need to use this:

    TrimmedInvNo: Left$([Inventory_Id],Len([Inventory_Id])-2)

    If you are doing it in VBA code then you will need to use this:

    lngTrimmedID = cLng(left$(cStr([Inventory_ID]), len(cStr([Inventory_ID])) -2))

    The cStr converts a Numeric value to a string, so you can use string manipulation on it.
    The cLng converts the final trimmed string back to a Long Integer.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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