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

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

Query expression:

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

mx

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

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

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)

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.

This did the trick! Thanks!

You are welcome.

mx

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,