Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Unanswered: trying to create automatic customer shipping to splice into separate field

    Hello!

    I'm wanting to set up a automated form button to cut out automated emails I receive and splice the information into separate field in the customers

    I get the same standard raw information such as:
    Billing Information--------
    Name: Mike Lopaplois
    Address1: 153359 Great Mill Rd
    Address2:
    City: Wooleybridge
    Postal Code: 22191
    State/Provence: VA
    Country: US
    Email: miike@aol.com
    Phone: 713-513-1132

    Is it possible to script a button to truncate the opening data (pasted in to the value [textDump(such as "NAME:", but cut all this vital data into the values in my [Customers] table - so it would read as:
    "Name: Mike Lopaplois" -> [CustomerName]
    "Address1: 153359 Great Mill Rd (and)
    Address2:" -> [ShipAddress1]
    "City: Wooleybridge" -> [ShipCity]
    "Postal Code: 22191" -> [ShipZip]
    "State/Provence: VA" -> [ShipState]
    "Country: US" -> [Ship Country]
    "Email: miike@aol.com" -> [Email]
    "Phone: 713-513-1132" -> [Phone]

    Right now, I'm copying and pasting each value which is a tedious task - any examples or advice are whole-heartidly welcomed!

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Do you already have or recognize anything like a line seperator in your TxtDump? Think you will need that, before making an attempt.

    I also once handled such a question through Automation to Word.
    Opened a Word instance from Access, copied raw text files to Word and handled them with Word macro's, resulting in clean normalized table-like input files.

    hth

  3. #3
    Join Date
    Mar 2002
    Posts
    20
    Originally posted by marion
    Do you already have or recognize anything like a line seperator in your TxtDump? Think you will need that, before making an attempt.

    I also once handled such a question through Automation to Word.
    Opened a Word instance from Access, copied raw text files to Word and handled them with Word macro's, resulting in clean normalized table-like input files.

    hth
    Hello!

    The text is formatted with line separators as every entry would be the same - that is:

    LINE 1: "Name: Mike Lopaplois" -> [CustomerName]
    LINE 2: "Address1: 153359 Great Mill Rd (and)
    LINE 3: Address2:" -> [ShipAddress1]
    LINE 4: "City: Wooleybridge" -> [ShipCity]
    LINE 5: "Postal Code: 22191" -> [ShipZip]
    LINE 6: "State/Provence: VA" -> [ShipState]
    LINE 7: "Country: US" -> [Ship Country]
    LINE 8: "Email: miike@aol.com" -> [Email]
    LINE 9: "Phone: 713-513-1132" -> [Phone]

    I was hoping to get some assistance in how to achieve truncating the "NAME:" text and then automatically assigning each line to a customer field by using a single push button on an Access form. Do you have any thoughts or seen any example code to achieve something like this by chance?

  4. #4
    Join Date
    Jan 2003
    Posts
    126

    Talking

    You can use something like this:

    Code:
    Dim StrVar As String
    Dim StrVar As String
       
    StrVar = "Phone: 713-513-1132"
    
    NewStrVar = Right(StrVar, Len(StrVar) - InStr(StrVar, ":"))
    it basically looks for the ":" and takes whatever is to the right of it.

    Ken

  5. #5
    Join Date
    Mar 2002
    Posts
    20
    Thanks for the assistance.

    I have the code which does work:


    Private Sub CutupTextDump_Click()

    Dim StrVar As String

    StrVar = "Phone: 713-513-1132"

    Me![PhoneNumber] = Right(StrVar, Len(StrVar) - InStr(StrVar, ":"))

    End Sub



    Which is able to create this value in the [PhoneNumber] field. The actualy [textDump] value is actually multi line such as:

    LINE 1 "Name: Mike Lopaplois" -> [CustomerName]
    LINE 2 "Address1: 153359 Great Mill Rd (and)
    LINE 3 Address2:" -> [ShipAddress1]
    LINE 4 "City: Wooleybridge" -> [ShipCity]
    LINE 5 "Postal Code: 22191" -> [ShipZip]
    LINE 6 "State/Provence: VA" -> [ShipState]
    LINE 7 "Country: US" -> [Ship Country]
    LINE 8 "Email: miike@aol.com" -> [Email]
    LINE 9 "Phone: 713-513-1132" -> [Phone]

    Are there some simple commands that I could pull the value LINE 9 of [TextDump] and let it be StrVar which would in turn put it into the [PhoneNumber] value?

    Thanks for any assistance!
    Jon

  6. #6
    Join Date
    Jan 2003
    Posts
    126
    How are you reading in the file?

    You would need to set StrVar to the current line. You could loop through the file to pull out the information.

    If you can attach a small file I can make one for you.

    Ken

  7. #7
    Join Date
    Mar 2002
    Posts
    20
    Originally posted by GA_KEN
    How are you reading in the file?

    You would need to set StrVar to the current line. You could loop through the file to pull out the information.

    If you can attach a small file I can make one for you.

    Ken
    Hi Ken,

    Thanks for your kind offer! The textDump value isn't necessarily a file I'm reading, but a copied and pasted value which is pasted into a field[textDump] - I've included a sample file with these values, but will also paste the actual copied and pasted info below:

    Shipping Information--------
    Name: Douglas F Baall
    Address1: 1446 Warner Ln #4
    Address2:
    City: Harmony
    Postal Code: 92392
    State/Provence: CA
    Country: US
    Email: dfbaall@msn.net
    Phone: 372-445-6113

    Thanks again for all your help!
    Jon
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2003
    Posts
    126
    Just a question..

    Are you pasteing one line at a time into the textbox?

    Can you send me the form your using?

    I have an idea of what you want, but I need to understand your process a little better.

  9. #9
    Join Date
    Mar 2002
    Posts
    20
    Originally posted by GA_KEN
    Just a question..

    Are you pasteing one line at a time into the textbox?

    Can you send me the form your using?

    I have an idea of what you want, but I need to understand your process a little better.
    Thanks kindly for your offer, Ken!

    As in the email I sent you, here is the copy of the sample form with an example textDump value pasted in (and I've manually copied and pasted the values where I'd hope the button would do it for me).

    Thanks for any help!
    Jon
    Attached Files Attached Files

  10. #10
    Join Date
    Jan 2003
    Posts
    126
    I got it, I'll take a look and post it back!

  11. #11
    Join Date
    Jan 2003
    Posts
    126
    Here you go, it works just fine, if you need explination of how it works just let me know. It's pretty straight forward though. Hopefully it'll give you exactly what you want.

    Ken
    Attached Files Attached Files

  12. #12
    Join Date
    Mar 2002
    Posts
    20

    Talking

    Originally posted by GA_KEN
    Here you go, it works just fine, if you need explination of how it works just let me know. It's pretty straight forward though. Hopefully it'll give you exactly what you want.

    Ken
    KEN!

    Most excellent -- it is EXACTLY what I needed and I have been using it all day. I really appreciate all your help!

    Is there by chance a way to quickly filter out odd characters such as quotes and commas (i.e. - "" ,) these confuse the database when it has to export out CSV files.

    Also is there a way to create a quick uppercase filter: in a case such as they list their name as:
    "HANK BLUES" or "hank blues", it would enter the value "Hank Blues"

    Thanks again for all your assistance as you've saved hours of manually cuting and pasting! I really appreciate all your help!
    Jon

  13. #13
    Join Date
    Jan 2003
    Posts
    126
    Sure, shouldn't be too hard, I'll take a look this morning and get back to you!

  14. #14
    Join Date
    Jan 2003
    Posts
    126

    Talking

    Here ya go, it removes " and , from the pasted string and sets the title case for the Customer name.

    If you have any questions let me know! Glad I could be of help!

    Ken
    Attached Files Attached Files

  15. #15
    Join Date
    Jan 2003
    Posts
    126
    Did this work for you?

    Ken

Posting Permissions

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