Access formula to calculate "theoretical" website from email address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi... I am a newby to Access and am trying to move from the Excel environment

I have a database of email addresses. They are in an access table with various fields.

Field#1 = emailaddress@emailaddress.

I would like to create a calculation infield#2 (or a query) that will search field#1 data, find the @ symbol, apend "www." to the portion of the email address that starts with the @ symbol (IE: @emailaddress).

This would yield a "theoretical web site address.

In Excel, I use a formula to do this like CONCATENATE("www.",MID(D1,((FIND("@",D1)+1)),25)), where D1 contains the email address - this results in something like www.xxxxxxxx.co

Any help would be greatly appreciated
 
first, suggest you do the calculation in a query - *usually* no good reason
to add calculated data to a field in a table. here's one way that should
work.

add the following procedure to a public module:

Public Function isWebAddr(ByVal strEmail As String) As String

Dim intPlace As Integer
intPlace = InStrRev(strEmail, "@")
isWebAddr = "www." & Right(strEmail, Len(strEmail) - intPlace)

End Function

use the function in your query as

SELECT isWebAddr([EmailFieldName]) AS Expr1
FROM TableName

substitute the correct table and field names, of course.

hth


Invatech said:
Hi... I am a newby to Access and am trying to move from the Excel environment

I have a database of email addresses. They are in an access table with various fields.

Field#1 = emailaddress@emailaddress.

I would like to create a calculation infield#2 (or a query) that will
Click to expand...
search field#1 data, find the @ symbol, apend "www." to the portion of the
email address that starts with the @ symbol (IE: @emailaddress).
This would yield a "theoretical web site address.

In Excel, I use a formula to do this like
Click to expand...
CONCATENATE("www.",MID(D1,((FIND("@",D1)+1)),25)), where D1 contains the
email address - this results in something like www.xxxxxxxx.com
 
Back
Top