Solved

Access 2007, formatting a hyperlink in a crosstab query to use in a form

Posted on 2010-09-14
10
766 Views
Last Modified: 2012-05-10
I have built a crosstab query (and the same happens in a regular query) to pull email info for my communities which is stored in the table as a hyperlink and when it returns the result, they come back in hyperlink format (i.e., laura.sheldon@laurasheldon.com#mailto:laura.sheldon@laurasheldon.com#) and I'd like to know if and how I can format this to return as just the email address, either in the CTQ/QRY or in the form that's based on it.
0
Comment
Question by:Laura Sheldon
  • 5
  • 4
10 Comments
 
LVL 75
ID: 33677557
One way:

Left("laura.sheldon@laurasheldon.com#mailto:laura.sheldon@laurasheldon.com#",Instr(1,"laura.sheldon@laurasheldon.com#mailto:laura.sheldon@laurasheldon.com#","#")-1)

returns:
laura.sheldon@laurasheldon.com
0
 
LVL 75
ID: 33677573
Query expression:

FmtEmaiAddr: Left([EMAIL_ADDRESS],InStr(1,[EMAIL_ADDRESS],"#")-1)

mx
0
 
LVL 75
ID: 33677588
Same expression can be used in the Control Source of a Text box on a Form or Report:

=Left([EMAIL_ADDRESS],InStr(1,[EMAIL_ADDRESS],"#")-1)

mx
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 10

Expert Comment

by:t_hungate
ID: 33677661
What view are you using to display the email addresses?  If you only have one filed that you are using on the form, if you set the fields hyperlink to null it should show up as plain old text.

Me.EmailAdd.HyperlinkAddress = ""

to set it back you can use something like this

Me.EmailAdd.HyperlinkAddress = "mailto:" & Me.EmailAddress

This will basically remove the hyperlink all together and then put it back when you are done.

TLH
0
 

Author Comment

by:Laura Sheldon
ID: 33682328
DatabaseMX,

In this SQL statement:

PARAMETERS [Start Date] DateTime;
TRANSFORM Sum(CommunityBillingTBL.[Bill Amount]) AS [SumOfBill Amount]
SELECT CommunityInfoListTBL.Community, CommunityContactsTBL.[Management Co], CommunityBillingTBL.[Contract Type], CommunityBillingTBL.[Service Description], CommunityBillingTBL.[Billing to Community (360)], CommunityBillingTBL.[Billing to Client (Fee)], IIf([Billing To Community (360)]=True,[DOCO] & ", " & [Doco Phone]) AS [360 Billing Contact], IIf([Billing to Community (360)]=True,[DOCO Email]) AS [360 Billing Contact Email], IIf([Billing To Community (360)]=True,[Address1] & ", " & [Address2] & " " & [City] & ", " & [State] & " " & [ZipCode]) AS [360 Billing Addy], IIf([Billing To Client (Fee)]=True,[ClientContact] & ", " & [ClientPhone]) AS [Fee Billing Contact], IIf([Billing to Client (Fee)]=True,[ClientEmail]) AS [Fee Billing Contact Email], IIf([Billing To Client (Fee)]=True,[ClientAddress1] & ", " & [ClientAddress2] & " " & [ClientCity] & ", " & [ClientState] & " " & [ClientZipCode]) AS [Fee Billing Addy], IIf([Billing to Community (360)]=True,[360 Billing Contact],[Fee Billing Contact]) AS [Billing Contact], IIf([Billing to Community (360)]=True,[360 Billing Contact Email],[Fee Billing Contact Email]) AS [Billing Contact Email], IIf([Billing to Community (360)]=True,[360 Billing Addy],[Fee Billing Addy]) AS [Billing Address], Sum(CommunityBillingTBL.[Bill Amount]) AS [Total Of Bill Amount]
FROM CommunityContactsTBL INNER JOIN (CommunityInfoListTBL INNER JOIN CommunityBillingTBL ON CommunityInfoListTBL.Community = CommunityBillingTBL.Community) ON CommunityContactsTBL.ID = CommunityInfoListTBL.ID
WHERE (((CommunityBillingTBL.[Bill Date])>=[Start Date]))
GROUP BY CommunityInfoListTBL.Community, CommunityContactsTBL.[Management Co], CommunityBillingTBL.[Contract Type], CommunityBillingTBL.[Service Description], CommunityBillingTBL.[Billing to Community (360)], CommunityBillingTBL.[Billing to Client (Fee)], IIf([Billing To Community (360)]=True,[DOCO] & ", " & [Doco Phone]), IIf([Billing to Community (360)]=True,[DOCO Email]), IIf([Billing To Community (360)]=True,[Address1] & ", " & [Address2] & " " & [City] & ", " & [State] & " " & [ZipCode]), IIf([Billing To Client (Fee)]=True,[ClientContact] & ", " & [ClientPhone]), IIf([Billing to Client (Fee)]=True,[ClientEmail]), IIf([Billing To Client (Fee)]=True,[ClientAddress1] & ", " & [ClientAddress2] & " " & [ClientCity] & ", " & [ClientState] & " " & [ClientZipCode])
ORDER BY CommunityBillingTBL.[Contract Type]
PIVOT CommunityBillingTBL.[Bill Date];

Where would your first reply go, exactly?

(Left("laura.sheldon@laurasheldon.com#mailto:laura.sheldon@laurasheldon.com#",Instr(1,"laura.sheldon@laurasheldon.com#mailto:laura.sheldon@laurasheldon.com#","#")-1)
0
 

Author Comment

by:Laura Sheldon
ID: 33682410
Also, if I'm returning results from several records so I need to know how to format this across the board rather than having a specific email address designated.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 33685886
"Where would your first reply go, exactly? "
My 2nd post @ http:#a33677573  is the better example.

For each email address field (if there is more than one) .... use an Expression like the example I gave.

mx
0
 

Author Closing Comment

by:Laura Sheldon
ID: 33702405
This did the trick! Thanks!
0
 
LVL 75
ID: 33703444
You are welcome.

mx
0
 

Author Comment

by:Laura Sheldon
ID: 33778768
DatabaseMX,

I used this in a form and it worked great, but can you tell me how, if possible, I can use it in a query? I tried it as an expression based on the email field I was trying to format but it didn't work.

Thanks,
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question