Access VBA 2010: Undefined function

[djsmith]

I have a function

Public Function SplitPart(LSTRSP_EML_EMAIL)
Dim LSTRSP_EML_EMAIL As String

'Dim LSTRSP_EML_DOMAIN As String

Split([LSTRSP_EML_EMAIL], "@", 2) = [LSTRSP_EML_EMAIL]


SplitPart_Exit:
    
    Exit Function

SplitPart_Err:
    MsgBox Error$
    Resume SplitPart_Exit
End Function

Which I have called in my query: LSTRSP_EML_DOMAIN: SplitPart().

I am getting an "undefined function "SplitPart' in Expression. I have verified my references in VBA--see attached file. So far I have only created and worked on this database using Access 2010 and have not attempted to open it in another version. OS Win 7 Professional. Not sure whether the problem is actually with my code or my references.

Your help, as always, is greatly appreciated.

[ruirib]

Hi,

Welcome to the Lounge.

Did you try to compile your function before using it?

[jeremybarker]

I have a function

Public Function SplitPart(LSTRSP_EML_EMAIL)
Dim LSTRSP_EML_EMAIL As String

'Dim LSTRSP_EML_DOMAIN As String

Split([LSTRSP_EML_EMAIL], "@", 2) = [LSTRSP_EML_EMAIL]


SplitPart_Exit:
    
    Exit Function

SplitPart_Err:
    MsgBox Error$
    Resume SplitPart_Exit
End Function

Which I have called in my query: LSTRSP_EML_DOMAIN: SplitPart().

I am getting an "undefined function "SplitPart' in Expression. I have verified my references in VBA--see attached file. So far I have only created and worked on this database using Access 2010 and have not attempted to open it in another version. OS Win 7 Professional. Not sure whether the problem is actually with my code or my references.

Your help, as always, is greatly appreciated.

Unless you've missed out or abbreviated a lot of code, I'm not sure how your function is supposed to work!

You haven't specified a return type for the function; your function definition doesn't include a line that returns a value from the function; you declare the function to take 1 input parameter but if you just call it with SplitPart() then you're not passing in any arguments; you have a local variable declared with exactly the same name as the function parameter; and the line that begins Split(...) = surely won't work because Split() is an inbuilt function that should appear on the right hand side of an assignment.

Very confused how this function will actually work (if it compiles at all)!

[djsmith]

No, but that could well be my problem.

This is a drawn out process that I am trying to simplify with code. An Excel file is created from a report pulled from an online web database. This report gives us all subscriptions even if they have expired or the person no longer has an active membership. The contents of this report are then copied to the clipboard.

My Function Leg_ENews() cleans out the data in the table, pastes the contents from the clipboard, Runs a couple of queries to clean out inactive records then a delete query is ran to clean out the old data and prepare the table for the new. Without the split function the rest of the code runs flawlessly. I may not be calling the Public Function correctly. Or figuring out how to compile the code as I do not understand a whole lot about programming.

Function Leg_ENews()
  
    
    DoCmd.OpenTable "Leg E-news", acViewNormal, acEdit
    DoCmd.RunCommand (acCmdSelectAllRecords)
    DoCmd.RunCommand (acCmdDeleteRecord)
    DoCmd.SetWarnings (False)
    DoCmd.RunCommand (acCmdSelectAllRecords)
    DoCmd.RunCommand (acCmdPaste)
    DoCmd.Close acTable, "Leg E-news"
    DoCmd.OpenQuery "Leg E-news_Expirey", acViewNormal, acEdit
    DoCmd.Close acQuery, "Leg E-news_Expirey"
    DoCmd.OpenQuery "Leg E-news_Remove_Duplicate_MBRCAT_CODE", acViewNormal, acEdit
    DoCmd.Close acQuery, "Leg E-news_Remove_Duplicate_MBRCAT_CODE"
    DoCmd.OpenQuery "Leg E-news_Delete_Query", acViewNormal, acEdit
    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery "Leg E-news_append", acViewNormal, acEdit    'LSTRSP_EMAIL_DOMAIN calls the Public Function SplitPart
    DoCmd.SetWarnings (False)
    DoCmd.OpenTable "Leg E-news_Final", acViewNormal, acEdit
    
    
    
Leg_ENews_Exit:
    
    Exit Function

Leg_ENews_Err:
    MsgBox Error$
    Resume Leg_ENews_Exit

End Function

Thank you very much for your assistance.

[djsmith]

I have attempted to address each of the points you made, I just am not sure I am really clear on some of the terms. Below is my updated SplitPart () with comments to see if I am understanding your points.

Public Function SplitPart(LSTRSP_EML_EMAIL As String) ' The email field is a string

Dim LSTRSP_EML_DOMAIN As String ' The domain is a string

LSTRSP_EML_DOMAIN = Split([LSTRSP_EML_EMAIL], "@", 2)   ' I am spliting LSTRSP_EML_EMAIL at the @ sign and passing the email domain to LSTRSP_EML_DOMAIN
Return  ' I am returning LSTRSP_EML_DOMAIN

' my query field is : LSTRSP_EML_DOMAIN: Split([LSTRSP_EML_EMAIL],"@",2)

SplitPart_Exit:
    
    Exit Function

SplitPart_Err:
    MsgBox Error$
    Resume SplitPart_Exit
End Function

No it still is not working. I just wish I could get a better handle on coding, I would love to understand it. Thank you for your patience.

[ruirib]

For a function to return a value, you need to assign the value to the name of the function:

SplitPart =

LSTRSP_EML_DOMAIN

[jeremybarker]

I have attempted to address each of the points you made, I just am not sure I am really clear on some of the terms. Below is my updated SplitPart () with comments to see if I am understanding your points.

Public Function SplitPart(LSTRSP_EML_EMAIL As String) ' The email field is a string

Dim LSTRSP_EML_DOMAIN As String ' The domain is a string

LSTRSP_EML_DOMAIN = Split([LSTRSP_EML_EMAIL], "@", 2)   ' I am spliting LSTRSP_EML_EMAIL at the @ sign and passing the email domain to LSTRSP_EML_DOMAIN
Return  ' I am returning LSTRSP_EML_DOMAIN

' my query field is : LSTRSP_EML_DOMAIN: Split([LSTRSP_EML_EMAIL],"@",2)

SplitPart_Exit:
    
    Exit Function

SplitPart_Err:
    MsgBox Error$
    Resume SplitPart_Exit
End Function

No it still is not working. I just wish I could get a better handle on coding, I would love to understand it. Thank you for your patience.

In addition to ruirib's comment, the "As String" in the function declaration needs to come outside the closing parenthesis. Also you seem to be missing an On Error Goto statement to tell your function to go to the error handling label when there is an error.

[djsmith]

Thank you both! I have removed the 'as string' from the function declaration. and declared it as a variable. Then I added SplitPart = LSTRSP_EML_DOMAIN before the Return.

I went through my VBA book and figured out the Goto on error, I added this statement 'On Error GoTo SplitPart_Err' above the variable declarations.

I have tried to debug the code. I think that because I am not calling this from another module the step through (compiler) does seem to be working. There is no table or query for the code to see the email field. Yes, I am rambling, but I am going to try to add code that would cause it to open the query and then run the SplitPart().

[ruirib]

When you code, there is an immediate window below the code window. You can call the function from there, with a syntax similar to this:

? SplitPart("AnExampleValue")

[Cronk]

The Split function will generate a string array, and in this case with email addresses, the first item will be the user name and the second the "domain" name.

Looking at the comment next to the docmd.openquery "Leg E-news_append", you only want the "domain".

If so, Split is not the best function.

Use
domain= mid(strEmailAddress, instr(strEmailAddress,"@") +1)

This will find the position of the @ in the email address, and give only characters after that position.

[djsmith]

Sorry, I have been tied up with a few other projects. Just tried the statement suggested by Cronk--Thank you a thousand times, works like a dream. I appreciate everyone's help out here.