Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    316854 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    44

    Translate Excel Formula into Access Formula


    I need this formula translated:

    =RIGHT($D4,LEN($D4)-FIND("@ ",$D4,1))

    Thanks

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not enough info. And my crystal ball is in the shop... don't know why they can't fix it!!

    What are you trying to do? Use lots of words.

    What is your table structure?

    Remember:
    No one can see your dB
    No one knows what the data looks like
    No one knows what the result should be....

  3. #3
    hansendl is offline Advanced Hobbyist
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    38
    Try this:

    Code:
    Public Function GetDomainFromEmail(strEmailAddress As String) As String
        Dim intLen As Integer
        Dim strOutput As String
    
        intLen = Len(strEmailAddress)
        strOutput = Right$(strEmailAddress, intLen - InStr$(1, strEmailAddress, "@"))
        
        GetDomainFromEmail = strOutput
        
    End Function

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @hansendl... Nice read.
    It was a long day

    I modified your function:
    Code:
    Public Function GetEmailDomain(strEmailAddress As String) As String
        GetEmailDomain = Mid(strEmailAddress, InStrRev(strEmailAddress, "@") + 1)
    End Function
    Use in a query:

    E_Domain: GetEmailDomain(NZ(Email,""))

    where EMail is the field in a table. The NZ() function is there just in case of null email address

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The VBA is not even necessary unless you want this procedure to be available from multiple locations.

    Can calculate in query or textbox.

    Mid([fieldname], InStrRev(Nz([fieldname],""), "@") + 1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Doh!!!
    Sometimes I get so wrapped up in using VBA, I forget the obvious.

  7. #7
    hansendl is offline Advanced Hobbyist
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    38
    @ssanfu...even better. I got so caught up in duplicating the Excel function as written that I didn't even think about Mid. @June7...nice!

    r/
    Dean

  8. #8
    316854 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    44
    Quote Originally Posted by June7 View Post
    The VBA is not even necessary unless you want this procedure to be available from multiple locations.

    Can calculate in query or textbox.

    Mid([fieldname], InStrRev(Nz([fieldname],""), "@") + 1)
    Thank you for all the replies.

    Here's what I have:

    John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

    I need a field that shows only 1234 N Main Ave Los Angeles CA 90001-1234.

    Thanks

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you try the suggestions? Another variation:

    Trim(Mid([fieldname], InStr(Nz([fieldname],""), "@") + 1))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    316854 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    44
    Quote Originally Posted by June7 View Post
    The VBA is not even necessary unless you want this procedure to be available from multiple locations.

    Can calculate in query or textbox.

    Mid([fieldname], InStrRev(Nz([fieldname],""), "@") + 1)
    Quote Originally Posted by June7 View Post
    Did you try the suggestions? Another variation:

    Trim(Mid([fieldname], InStr(Nz([fieldname],""), "@") + 1))
    These formulas did not work. I changed fieldname to the proper field name. The query was blank.

    I need this:

    John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

    Queried into this:

    1234 N Main Ave Los Angeles CA 90001-1234.

    Thanks

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't know why it didn't return any values. Works perfectly for me. Would have to see your dB.
    Open Query1.....

  12. #12
    316854 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    44
    I see what I did wrong.

    I put the formula in the criteria field.

    I put the formula in the proper field and now it works.

    Thanks

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great.
    Ready to mark this solved?

  14. #14
    316854 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    44
    No.

    I need this:

    1234 N Main Ave Los Angeles CA 90001-1234.

    to show up in the field next to this:

    John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

    but I need it done in the form (rather than / as well as) the query.

    Thanks

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If it is in the query for the form, it is available to add to the form.

    You do have a query as the record source for the form, right?

    Or am I misunderstanding?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using an Excel Formula in Access
    By BEI77 in forum Access
    Replies: 6
    Last Post: 05-08-2014, 05:08 PM
  2. Anyone able to translate a formula from excel?
    By dniezby in forum Programming
    Replies: 10
    Last Post: 05-21-2013, 01:10 PM
  3. Excel Formula into Access Query
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 06-25-2012, 06:46 AM
  4. Excel Formula Needed in Access
    By bmschaeffer in forum Queries
    Replies: 4
    Last Post: 01-18-2012, 01:13 PM
  5. Replies: 0
    Last Post: 09-03-2009, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums