Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    USA
    Posts
    8

    Unanswered: Expression question from a new member

    I have an email field that automatically stores data as a mailto hyperlink:

    #mailto:joe@abc.com#

    Is there an expression I can use to manipulate the data so that it returns just:

    joe@abc.com

    ..on a report? I've tried several different "Trim()" functions, but I can't seem to get the syntax right to get it to work.

    Any help would be appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    60
    Try (one of many ways):

    strEmail="#mailto:joe@abc.com#"

    strEmail=Left(strEmail,Len(strEmail)-1) 'Strip off trailing #
    strEmail=Right(strEmail,Len(strEmail)-8) 'Strip off #mailto:

    strEmail now contains just the email address

    You could reduce this to one line by using Mid function:
    strEmail=Mid(strEmail,9,Len(strEmail)-9)

  3. #3
    Join Date
    Sep 2003
    Location
    USA
    Posts
    8
    Thanks for looking into this problem for me. I'm still getting #Error after inputting in what you had suggested. The logic of your expression makes perfect sense, so I must be doing something wrong.

    I'm laying out a report and I'm basically inputting your expression into the "control source" property of the email field. However, I keep getting #Error. Is there a step I am missing?

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Expression question from a new member

    Originally posted by eliao73
    I have an email field that automatically stores data as a mailto hyperlink:

    #mailto:joe@abc.com#

    Is there an expression I can use to manipulate the data so that it returns just:

    joe@abc.com

    ..on a report? I've tried several different "Trim()" functions, but I can't seem to get the syntax right to get it to work.

    Any help would be appreciated.
    First of all - I would suggest that you go to the table and strip the # signs out of the table, before feeding the data into anything else. The # sign is used to designate a date and what Access is doing is assuming that you are representing a date in the string. Probably just automatically run two update queries in the processing.

    Just my 2 centavos....
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Sep 2003
    Location
    USA
    Posts
    8
    I'm afraid that I need to retain the "#'s" on the data, as I need for the data to be stored as mailto links in my database so that when a person clicks on the field, it opens up the default email application.

    My problem is pulling the data and presenting it on a regular report for printing. It looks silly if the report displays "#mailto:joe@abc.com#" instead of just the email address. I tried bc301's initial suggestion by pasting the MID() expression into the VB code, and it worked, except for that it stripped out the #mailto:# in the original data too. I'd like for the data to remain intact, but have it displayed as joe@abc.com in just the printed report.

    Any other suggestions? Thanks.

  6. #6
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by eliao73
    I'm afraid that I need to retain the "#'s" on the data, as I need for the data to be stored as mailto links in my database so that when a person clicks on the field, it opens up the default email application.

    My problem is pulling the data and presenting it on a regular report for printing. It looks silly if the report displays "#mailto:joe@abc.com#" instead of just the email address. I tried bc301's initial suggestion by pasting the MID() expression into the VB code, and it worked, except for that it stripped out the #mailto:# in the original data too. I'd like for the data to remain intact, but have it displayed as joe@abc.com in just the printed report.

    Any other suggestions? Thanks.
    Are you making the controlsource property "=<formula>" ? Is the name of the box different from your controlsource and any ohter boxes, and or captions.

    Just asking the dumb questions?
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  7. #7
    Join Date
    Sep 2003
    Location
    USA
    Posts
    8
    Yes, the name of the box and controlsource are the same. In the controlsource property, I have been trying different "=(formula)" expressions "(Left(), Right() Trim())", but with no success (#Error).

    Any suggestions? Thanks again.

  8. #8
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by eliao73
    Yes, the name of the box and controlsource are the same. In the controlsource property, I have been trying different "=(formula)" expressions "(Left(), Right() Trim())", but with no success (#Error).

    Any suggestions? Thanks again.
    Change the names to be different and try that. You can get the errors from objects being named the same as the caption, titles, and controlsource, text boxes.

    Maybe the first thing in the formula would be to make it
    =Mid(cstr(strEmail),9,Len(cstr(strEmail))-9)
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  9. #9
    Join Date
    Sep 2003
    Location
    USA
    Posts
    8

    Talking

    Jim P.,

    Hey, it worked! Thank you so much for figuring out this problem! Hope your company pays you well, because you deserve every penny! Thanks again for your expertise!

    E. Liao

  10. #10
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by eliao73
    Jim P.,

    Hey, it worked! Thank you so much for figuring out this problem! Hope your company pays you well, because you deserve every penny! Thanks again for your expertise!

    E. Liao
    Pay is never well enough...but I have a job.....

    Glad it worked.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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