Solved

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

Posted on 2010-09-14
10
759 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

11 Experts available now in Live!

Get 1:1 Help Now