?
Solved

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

Posted on 2010-09-14
10
Medium Priority
?
811 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 - Microsoft MVP, Access and Data Platform) earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

850 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