Access storing email addresses

[cbende2]

Hello all,



I'm having a little problem on how access is storing email addresses. Many emails are being stored as Myemailaddress@gmail.com#mailto:Myem...ss@gmai l.com#

This is a problem because when I run a wordmerge, it pulls the email address and the "#mailto:#" part is included. I've done a manual scan to delete the #mailto:# part off the emails, but now several records have it again.

The only solution I'm thinking of is having an update query run before the wordmerge starts to remove that part(#mailto:#). I'm just not sure what the syntax would be in SQL for the query.

Any help would be great, thanks!

[pbaldy]

Are you using the hyperlink data type (or format perhaps)? It stores that way. I would use the text data type.

[amrut]

Following expressions give the First and Last occurrence (position) of # within your field.
FirstOccurrence : InStr(1,[EmailAddress],"#")
LastOccurrence : InStrRev(EmailAddress],"#")
You can then use the string functions Left/Mid/Right to get your desire output.

[cbende2]

Are you using the hyperlink data type (or format perhaps)? It stores that way. I would use the text data type.

Yep, I am using the text data type field.

[cbende2]

Following expressions give the First and Last occurrence (position) of # within your field.
FirstOccurrence : InStr(1,[EmailAddress],"#")
LastOccurrence : InStrRev(EmailAddress],"#")
You can then use the string functions Left/Mid/Right to get your desire output.

Could you explain a little more what this does amrut? And would that be criteria of the query for the email field? I'm not sure what to do with the SQL you just gave me.

[amrut]

For ex. if the your email is like Myemailaddress@gmail.com#mailto:Myemss@gmai l.com#
FirstOccurrence : InStr(1,[EmailAddress],"#") is 25
LastOccurrence : InStrRev(EmailAddress],"#") = length of the email address field
The required mail address is Mid([EmailAddress],1,[FirstOccurrence]-1)
The LastOccurrence is not required if there you need the string before the first # tag only.
The SQL looks like

SELECT tblCandidates.Candidate, InStr(1,[EmailAddress],"#") AS FirstOccurrence, InStrRev([EmailAddress],"#") AS LastOccurrence, Mid([EmailAddress],1,[FirstOccurrence]-1) AS ReqdMailAddress
FROM tblCandidates;

Hope this helps.

[cbende2]

Ok, I'll tinker with this for a bit, thanks amrut and pbaldy.

And yeah I don't have to worry about any text after the first "#"

I won't mark as solved until I've figured it out! I may need more help

[cbende2]

Ok so I got that query working, Now how would I make the [ReqdMailAddress] replace the existing one?

and also, for all emails that didn't have the "#mailto:#" the query returned them as #Func!, I'm guessing thats how its supposed to be.

[amrut]

Now how would I make the [ReqdMailAddress] replace the existing one?

Use the expression in an UPDATE query to replace the existing records

for all emails that didn't have the "#mailto:#" the query returned them as #Func!

In this case the FirstOccurence must be 0, use <>0 in the criteria to exclude those records.

[cbende2]

Hmm, when I change and run it as an update query it just returns the one column "Email" and doesn't make the change. In the query builder i had "Email" updating to "ReqdMailAddress" is that correct?

[amrut]

Example SQL for update query

UPDATE tblCandidates SET tblCandidates.EmailAddress = Mid([EmailAddress],1,InStr(1,[EmailAddress],"#")-1);

[cbende2]

I'm very slow at this... Ok so that SQL just returns the emails without any change, the other SQL you gave me actually shows the change but it doesn't apply the update.

Am I supposed to join the two SQL statements together?

Why am I so lost at this right now? lol, I feel like its really simple. I just want to get rid of the # and anything that follows and update that as the new record.

EDIT: So I ran the update SQL you provided and it deleted all the Emails that didn't have "#mailto:# however, the ones that had #mailto# are now correct! How would I set the criteria in the more recent SQL you gave me to only apply it to Emails that contain "#" or "#mailto:"

EDIT 2: What am I doing wrong?

UPDATE [Contact Table] SET [Contact Table].[Email - Primary] = Mid([Email - Primary],1,InStr(1,[Email - Primary],"#")-1)
WHERE ((([Contact Table].[Email - Primary]) LIKE '%#mailto%'));

EDIT 3: Got it! Thanks for helping amrut

[amrut]

Add a column to query having expression InStr(1,[EmailAddress],"#") and put >0 in criteria row. Leave the "Update to" blank for this column.