Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (2003)

    dim a as integer, b as integer
    a=instr([Body of email],"<")
    b=instr([Body of email],">")

    new column: mid([Body of email],a+1,b-a+1)

  2. #2
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access (2003)

    Thank you for the statement. I apologize for the ignorance but can you tell me where I need to place this statement? Do I paste it in a module? Then add the column to my query?

    Thank you in advance.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access (2003)

    You can combine all this into a single column of a query as below, assuming you just want to display the results in the query. If you wanted to store the address you would need to convert this into an update query.

    Email: Mid([Body of email],InStr([Body of email],"<")+1,InStr([Body of email],">")-InStr([Body of email],"<")-1)

    When I tested this I needed to replace the +1 from the previous reply with -1.

    If any of the records do not have < and > this will give an error.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access (2003)

    PERFECT!!! Thank you. You are the best.
    <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33>

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Extract part of a field (Access 2003)

    Subject edited by HansV - "Access" was a bit vague

    I have a table that contains the following fields:
    To:
    Date:
    Body of email:

    The fields are all pulling data from the Outlook database. In any case the "Body of email" field contains alot of information but I just need to extract the part of the field that contains the email address. Email Address is in between the these two signs < >. So my question is, Is there a way that I can extract the data into a column that I can query aganist?

    Long story short is that I received a bunch of email addresses that no longer exist so I need to query on them so I can remove them out of my next email distribution.

    Any help would be fantastic!

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access (2003)

    Hello John,

    When I tested this in my production database, I received a data type mismatch error. I think it's because the [BodyOfEmail] field is a memo field and the formula is a INSTR. Do you know what the appropriate expression would be for a memo field?

    Thank you in advance!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access (2003)

    InStr should work with a memo field as well as with a text field. I tested it just now, and John Hutchison's expression works fine. So there must be something else that causes the error. Check your query very carefully for typos etc.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access (2003)

    You are right! It did work. But, when I try exporting into excel, it doesn't allow me to. I get a message that it could not append and it will delete my records. Do you know why?

    Also, the formula works great but is there a formula that extracts the email address from a memo field? Not all email addresses are in between the two characters< >

    Thank you in advance for your help

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access (2003)

    Without seeing the query and data I have no idea what causes the error message.

    If some of the e-mail addresses are enclosed in < > and others aren't, it's going to be quite complicated to extract the e-mail address. Using VBA code would probably be easier than trying to use expressions with built-in functions. And it would be a lot easier if the data used a consistent format.

Posting Permissions

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