Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Trim Left 3 letters

    I am trying to figure out how to trim the first 3 letters in a code.

    Code:
    Me.tpPOID = Format(Me.tpDateEntered, "yyyymmdd") & "_" & Left([me.tpProductID]) & "_" & Me.tpPrintingCode
    This 20131014_ENGFFF_13OCT150M is what I want the end result to be from the following data: 10/14/13, TRAENGFFF, 13OCT150M

    The code in red is what I'm having problems with. How can I remove the TRA from the ProductID?

  2. #2
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    Code:
    Me.tpPOID = Format(Me.tpDateEntered, "yyyymmdd") & "_" & RIGHT([me.tpProductID],(LEN([me.tpProductID])-3)) & "_" & Me.tpPrintingCode
    ?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This is the message I receive with that.



    RightErrorMessage.png

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Schultz,

    What is the context of the code, e.g. is it code from a Form, Query, Report, or a module referencing a dataset? Are the items Me.... Form fields or Dataset fields? Could you post the entire routine from Sub to End Sub?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    You'd want to use: Mid(tpProductID,4)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    Code:
    Me.tpPOID = Format(Me.tpDateEntered, "yyyymmdd") & "_" & RIGHT(me.tpProductID,(LEN(me.tpProductID)-3)) & "_" & Me.tpPrintingCode
    ?

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry, I should have been more explicit. I am using this on a form, trying to get a Purchase Order ID from the date we enter the PO, plus the Product ID (minus the 3 first letters which indicate a category which for this form we don't need the category), plus the printing code that gets printed on the product that tells us how many were in the printing lot. I realize that's an awful long code to store, but I'm going to use it in my tblInventoryReceived to calculate how much product we did receive, as the product isn't always delivered in one shipment. So, I will have a calculated field that will calculate how much of the product has been delivered, and once it equals the amount ordered, the Purchase Order will be closed.

    Below is the code that I finally managed to get it to work. I had to use the full hierarchy with forms to get it to work.

    Code:
    Private Sub tpQuantityOrdered_AfterUpdate()
        Me.tpPOID = Format(Me.tpDateEntered, "yyyymmdd") & "_" & Right(Forms![frmTractPurchases]![tpProductID], (Len(Forms![frmTractPurchases]![tpProductID]) - 3)) & "_" & Me.tpPrintingCode
    End Sub

  8. #8
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    492
    Thanks
    10
    Thanked 52 Times in 43 Posts
    Looks good.

Posting Permissions

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