Email Addresses

[Jerry McNabb]

In Access 2002, I have a table which contains a column EmailAddress defined
as a hyperlink. When I run a particular select query, some of these addresses
have new data appended to them. For example, if I had an email address of
(e-mail address removed) the query might show it as
[email protected]#http://[email protected]#.

This is not in the table, but it passes through a union query and into a
report. It seems to be strictly at random. I can find no commonality among
those "doubled" entries.

I tried changing the field to a text field. Access warned me that I had
shortened a field and data might be lost. I had not changed the length so I
continued. Now the table shows all the EmailAddress fields to have been
doubled in the same way. The query shows all of them as the table does. I
changed back to hyperlink and everything went back to the way they were.

 

[Daniel]

Post your Union query sql so we can take a look.

Daniel P

 

[Jerry McNabb]

The union query is
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory1]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory1D]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory1N]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory1R]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory1RN]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory2]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory3]
Union
SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory4]

UNION SELECT
[SortKey],[RecordType],[SortRecordType],[ClassYear],[Name],[Comment],[PhoneNote] from [qryDirectory5];

The problem I was describing is in qryDirectory4 which is
SELECT [LastName] & [FirstName] & [MiddleName] AS SortKey, "4 " AS
RecordType, Val([RecordType]) AS SortRecordType, " " AS
ClassYear, (IIf(IsNull([Address1] & [Address2] & [City] & [StateCode] &
[PostalCode])," ",(IIf([PublishAddress],([City] & " " & [StateCode] & " " &
[PostalCode])," ")))) AS Name, (" ") AS
Comment, (IIf(IsNull([EmailAddress]),"eMail Address not
Available",(IIf([PublishEmail],[EmailAddress],"eMail Address Unpublished"))))
AS PhoneNote
FROM tblAlumnus
WHERE (((tblAlumnus.Living)=True))
ORDER BY [LastName] & [FirstName] & [MiddleName];

 

[Dale Fye]

Jerry,

I don't like hyperlink fields for just that reason. I create textfields and
then in my forms use command buttons to initiate the hyperlink. This works
for email addresses as well for hyperlinks to documents or URLs.

In my command button, I'll have code similar to:

Private sub cmd_Email_Click

if len(me.txt_Email & "") > 0 then application.followhyperlink
me.txt_Email

End

HTH
Dale

 

[FredFred]

For better or worse a hyperlink field is supposed to have all of that in it
(even if not displayed) First is the display name for the link, then the
actual url for the link etc. If you don't want all that you should probably
use a text field.