Solved

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

Posted on 2010-09-14
10
763 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now